﻿#coding=utf-8
import copy
import cPickle
import dbmngr
from myLogger import *
import datetime
import kit
import config

# WareHouse whList[]
##def insertSql(table, filedList):
##    str1 = 'insert into %s (' %(table) 
##    str2 = 'values('
##    param = []
##    
##    for i in range(len(filedList)):
##        str1 += filedList[i][0]
##        str1 += ','
##        str2 += '%s,'
##        param.append(filedList[i][2])
##    
##    sql = str1[0:-1] + ')' + str2[0:-1] + ')'
##    
##    print u'执行sql'
##    print sql
##    print param
##           
##    return dbmngr.dbmngr.execute(sql, param)

def insertSql(table, feilds, vals):
    str1 = 'insert into %s (' %(table) 
    str2 = 'values('
    
    for i in range(len(feilds)):
        str1 += feilds[i]
        str1 += ','
        str2 += '%s,'
    
    sql = str1[0:-1] + ')' + str2[0:-1] + ')'
    
    print u'执行sql'
    print sql
    print vals
    
    return dbmngr.dbmngr.execute(sql, vals)
    
##def createObj(className):
##    print u'createDataoObj %s' %(className)
##    if (className == CDataCategory.__name__):
##        return CDataCategory()
##    elif (className == CDataProduct.__name__):
##        return CDataProduct()
##    else:
##        print u'Unkonwn Class %s' %(className)
##        return None
        
##def selectAllSql(table, dataList, className):
##    sql = u'select * from '+table
##    cnt = dbmngr.dbmngr.execute(sql)
##    ret = dbmngr.dbmngr.cursor.fetchall()
##
##    for item in ret:
##        new = createObj(className)
##        for j in range(len(item)):
##            new.m_field[j][2] = item[j]
##        dataList.append(new)

##def selectAllSql(table, dataList, classType):
##    sql = u'select * from '+table
##    cnt = dbmngr.dbmngr.execute(sql)
##    ret = dbmngr.dbmngr.cursor.fetchall()
##
##    for item in ret:
##        #new = createObj(className)
##        new = classType()
##        for j in range(len(item)):
##            new.m_fieldVals[j] = item[j]
##        dataList.append(new)
        
def abstract():
    raise NotImplementedError('abstract')
        
class CDataModel(object):
    tbl = ''
    m_dataList = []
    dataLoaded = False # 用时导入
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    m_fieldDefVals = [] #重构完成之后才能使用
    
    listenerList = []
    
    #True会导致新建对象时无法获取id，实体信息类慎用
    # 关系型数据库可以没有id, fi_key=-1
    autoId = True
    fi_key = 0
    COL_KEY = 0
    
    enableObserver = False
    
    def __init__(self):
        if self.__class__ is CDataModel:
            abstract()
    
        # 有一些子类没有用appendField，所以下一行还有用
        if not self.m_fieldDefVals:
            self.m_fieldVals = [''] * len(self.m_fieldNames)
        else:
            self.m_fieldVals = self.m_fieldDefVals[:]
        
    @classmethod    
    def initClsData(cls, loadData=False):
        '''系统启动时调用，子类需要重写该函数以完成初始化'''
        cls.m_dataList = []
        cls.m_fieldNames = []
        cls.m_fieldCNNames = []
        cls.m_fieldDefVals = []
        cls.listenerList = []
        
        cls.makeField()
        
        if loadData:
            cls.refreshDataList()

    @classmethod
    def makeField(cls):
        '''子类覆盖此函数，定义数据库字段'''
        cls.fi_id = cls.appendField('id', defVal=-1)
    
    @classmethod        
    def appendField(cls, name, cnName='', defVal=''):
        '''TODO - 重构时添加defVal，以便createNew()中使用(cls.m_fieldDefVals)
            TODO - 重构时考虑是否用initFields替代(貌似没有多少必要?)
        '''
        cls.m_fieldNames.append(name)
        cls.m_fieldCNNames.append(cnName)
        cls.m_fieldDefVals.append(defVal)
        return len(cls.m_fieldNames) - 1
    
    #def appendExtraVal(self, val):
    #    '''添加额外的组成数据，以便UI调用时提高效率'''
    #    self.m_fieldVals.append(val)
    #    return len(self.m_fieldVals) - 1
            
    @classmethod        
    def initFields(cls, fieldNames):
        '''fieldNames - [(name, cnName),... ]
           TODO - 重构时添加defVal，以便createNew()中使用
        '''
        index = []
        for name,cnName in fieldNames:
            index.append(cls.appendField(name,cnName))
        return index
        
    @classmethod
    def getObjList(cls):
        '''以列表形式返回数据对象列表，供ctrl遍历数据使用(以免暴露内部实现)'''
        
        # 暂时不使用缓存，看看哪里有问题
        print (cls.tbl, 'try to getObjList')
        if cls not in [CDataProduct, CDataCategory]:
            print (cls.tbl, 'refetch')
            return cls.fetch()
    
        if (not cls.dataLoaded):
            cls.refreshDataList()
        return cls.m_dataList
            
    @classmethod
    def getData(cls, fields):
        '''根据字段查找对象实例，以第一个找到的为准
            fields - {'field':val,...}
        '''        
        for obj in cls.getObjList():
            bFind = True
            for field,val in fields.items():
                #print '[getData]%s %s' %(val, obj.m_fieldVals[cls.getFieldNo(field)])
                if val.__str__() != obj.m_fieldVals[cls.getFieldNo(field)].__str__():
                    #print '[getData]%s %s' %(type(val.__str__()), type(obj.m_fieldVals[cls.getFieldNo(field)].__str__()))
                    #print '[getData]%s %s' %(val, obj.m_fieldVals[cls.getFieldNo(field)])
                    bFind = False
                    break
            if bFind:
                return obj
        return None
            
    @classmethod
    def getInstance(cls, fields):
        print '[getInstance]'
        #如果没有导入数据库，则初始化之
        if (len(cls.m_dataList) == 0):
            cls.refreshDataList()
        obj = cls.getData(fields)
        
        # 160903
        if not obj:                
            cls.refreshDataList()
            obj = cls.getData(fields)
        
        return obj
    
    @classmethod
    def removeInstance(cls, obj=None, fields={}):
        if (obj is None):
            obj = cls.getFirstInstance(fields)
        
        if (obj is not None and cls.m_dataList):
            try:
                cls.m_dataList.remove(obj)
            except:
                print u'removeInstance发生错误.cls:%s len:%d obj:%s' %(cls, len(cls.m_dataList), obj.m_fieldVals[0])
                
    @classmethod
    def getItemCount(cls):
        return len(cls.m_dataList)
    
    @classmethod 
    def getFieldNo(cls, field):
        #print 'getFieldNo %s %s %d' %(cls.__name__, field, len(cls.m_fieldNo))
        #print '%d %d %d' %(len(CDataModel.m_fieldNo), len(CDataProduct.m_fieldNo), len(CDataCategory.m_fieldNo))
        if (len(cls.m_fieldNo) == 0):
            #print 'initing...'
            for i in range(len(cls.m_fieldNames)):                
                cls.m_fieldNo[cls.m_fieldNames[i]] = i
                #print '%s %d' %(cls.m_fieldNames[i], i)
        return cls.m_fieldNo[field]  
    
    @classmethod
    def getFirstFieldVal(cls, inField, outField, byIndex = False):
        '''inField - {'field':xx,}
            outField - 'field'
        '''
        if (byIndex == True):
            #inField - {COL_xx:val,} outField - COL_xx
            return cls.getFirstInstance(inField).getFieldVal(index=outField)
        
##        if (cls is CDataDelivery):
##            return 'None %s' %outField
        
        return cls.getData(inField).getFieldVal(outField)
    
    @classmethod
    def findItemValue(cls, inField, outField): 
        '''inField - {'field':xx,}
            outField - 'field'
        '''
        obj = cls.getFirstInstance(inField)
        if obj is None:
            #print 'Error:None cust %d' %saleOrder.getFieldVal('peerId')
            print 'Error:None Object'
            return None
        else:
            return obj.getValue(outField)
                
    @classmethod
    def getFirstInstance(cls, field):
        '''
            从m_dataList查找，需要确保已装载数据
            inField - {COL_xx:val,}
            return - object
        '''
        
        objList = cls.fetch(cls._convertSqlCondition(field))
        if objList:            
            return objList[0] 
        else:
            print u'getFirstInstance发生错误.cls:%s  fields:%s' %(cls, field)
            return None
        
        #print 'getFirstInstance begin'
        index = field.keys()[0]
        for obj in cls.m_dataList:
            #print type(obj.m_fieldVals[index])
            #print obj.m_fieldVals[index]
            if (obj.m_fieldVals[index] == field[index]):
                return obj
            
        log(LOG_ERR, 'unknown obj: %s[%d](%s)%s', (cls, index, type(field[index]), field[index]))
        return None
    
    @classmethod
    def findObj(cls, keyVal):
        '''没有主键的数据表，不可使用该函数'''
        
        objList = cls.fetch(cls._convertSqlCondition({cls.fi_key:keyVal}))
        if objList:            
            return objList[0] 
        else:
            print u'findObj发生错误.cls:%s  keyVal:%s' %(cls, keyVal)
            print u'sql:%s'%cls._convertSqlCondition({cls.fi_key:keyVal})
            return None
        
        
        for obj in cls.m_dataList:
            if (obj.m_fieldVals[cls.fi_key] == keyVal):
                return obj
   
        # 160903
        
        
        cls.refreshDataList()
        for obj in cls.m_dataList:
            if (obj.m_fieldVals[cls.fi_key] == keyVal):
                return obj
        
        log(LOG_ERR, 'unknown obj: keyVal %s', keyVal)
        return None
        
    @classmethod
    def getObjs(cls, fields):
        '''fields - {COL_xx:val,}'''
        
        #if (len(cls.m_dataList) == 0):
        #    cls.refreshDataList()
            
        list = []
        for obj in cls.getObjList():
            match = True
            for index,val in fields.items():
                if (val != obj.getValue(index)):
                    match = False
                    break
                
            if(match):
                list.append(obj)

        return list
    
    @classmethod
    def getInstanceFromDB(cls):
        pass
        
    def getFieldVal(self, field='',index=-1):
        if(index == -1):
            index = self.getFieldNo(field)
        
        return self.m_fieldVals[index]
    
    def getKeyVal(self):
        return self.m_fieldVals[self.fi_key]
        
    def getValue(self, index):
        return self.m_fieldVals[index]
    
    def setValue(self, index, val):
        self.m_fieldVals[index] = val
        
    def setFieldVal(self, val, field='', index=-1):
        if(index == -1):
            index = self.getFieldNo(field)
        self.m_fieldVals[index] = val
    
    def setMulFieldVal(self, fields):
        '''一次设置多个字段值
        fields = {COL_xx:val, }
        '''
        for (col,val) in fields.items():
            self.m_fieldVals[col] = val
    
    def getFieldCnt(cls):
        return len(cls.m_fieldNames)
            
    @classmethod 
    def getCNName(cls, field='', index=-1):
        if (index == -1):
            return cls.m_fieldCNNames[cls.getFieldNo(field)]
        else:
            return cls.m_fieldCNNames[index]
    
    def populateExtra(self):
        '''渲染不存储在数据表中的对象变量，如saleOrder的(un)paid
            - 在insert/update时，新建数据或更新数据时需要渲染
        '''
        pass
    
    @classmethod    
    def listen(cls):
        '''extra data的获取方式决定应该listen那些类'''
        pass
    
    @classmethod
    def attach(cls, func):
        cls.listenerList.append(func)
    
    def notify(self, op=''):
        '''通知listner发生了变化'''
        if not self.enableObserver:
            return
        
        print ('notify', op, self, self.listenerList)
        for item in self.listenerList:
            item(op, self)
            
    
    @classmethod
    def createNew(cls):
        '''工厂方法 - 主要目的是封装id分配方式，注意子类中如果不是自动int增量，
            需要覆盖 self.assignNewId()'''
        return cls.factory()
    
    @classmethod
    def getNewId(cls):
        '''子类可以覆盖，以定义自己的id产生方式'''
        return cls.newAutoIncrId()
        #return cls.newBillId()
    
    @classmethod
    def factory(cls, newId=True):
        obj = cls()
        if newId and cls.fi_key >= 0:
            obj.setValue(obj.fi_key, obj.getNewId())
            #if cls.autoId:
            #    obj.setValue(obj.fi_key, obj.newAutoIncrId())
            #else:
            #    obj.setValue(obj.fi_key, obj.newBillId())
                
        return obj
    
    def has_key_val(self):
        if (self.fi_key >= 0):
            try:
                # 因为有string id，所以这种判断方式不对
                if int(self.m_fieldVals[self.fi_key]) > 0:
                    return True
            except:
                return False
            
        return False
    
    def insert_with_autoid(self):
        field_str = []
        field_val = []
        for i in range(len(self.m_fieldNames)):
            if i==self.fi_key:
                continue
            field_str.append(self.m_fieldNames[i])
            field_val.append(self.m_fieldVals[i])
            
        sql = 'insert into %s (%s)values(%s)' %(self.tbl, 
                    ','.join(field_str), 
                    ','.join(['%s']*len(field_val)))
        print sql
        cnt = dbmngr.dbmngr.execute(sql, field_val)

        if (cnt > 0):
            self.__class__.m_dataList.append(self)
            self.populateExtra()
            self.notify('insert')
            
        return cnt
            
    def insert(self):
        '''Note:增、删、改都要考虑到其他相关表的信息是否需要修改'''
        #if not self.has_key_val():
        #if auto_id:
         #   return self.insert_with_autoid()
            
        sql = 'insert into %s values(' %(self.tbl)
        for i in range(len(self.m_fieldNames)):
            sql += '%s,'
        sql = sql[:-1] + ')'
        
        print (sql, self.m_fieldVals)
        cnt = dbmngr.dbmngr.execute(sql, self.m_fieldVals)

        if (cnt > 0):
            self.__class__.m_dataList.append(self)
            self.populateExtra()
            self.notify('insert')
            
        return cnt
    
    @classmethod
    def quickInsert(cls, fields, newId=True):
        obj = cls.factory(newId)
        obj.setMulFieldVal(fields)
        if obj.insert() > 0:
            return obj
        
        return None
    
    @classmethod
    def tryInsert(cls, fieldDict):
        '''返回id'''
        obj = cls.factory()
        obj.setMulFieldVal(fieldDict)
        cnt = obj.insert()
        if (cnt <= 0):
            obj = cls.getFirstInstance(fieldDict)
        return obj.getValue(cls.COL_ID) if obj else -1
        
    @classmethod
    def deleteRefItem(cls, obj):
        '''子类根据需要选择删除关联数据表中的数据'''
        pass
    
    @classmethod
    def deepDeleteItem(cls, obj):
        return obj.deepDelete()
    
    @classmethod
    def deepDeleteById(cls, keyVal):
        obj = cls.findObj(keyVal)
        if obj:
            return cls.deepDeleteItem(obj)
        
        return 0
            
    def deepDelete(self):
        self.deleteRefItem(self)
        return self.delete()
    
    @classmethod
    def deleteItem(cls, obj):
        obj.delete()
                
    def delete(self):
        if self.fi_key >= 0:
            #sql = 'delete from ' + self.tbl + ' where %s=' %(self.m_fieldNames[self.fi_key])
            #sql += '%s'
            sql = 'delete from '+self.tbl+' where '+self.m_fieldNames[self.fi_key]+'=%s'
            param = (self.m_fieldVals[self.fi_key])
        else:
            sql = 'delete from %s where ' %cls.tbl
            #sql += cls._convertSqlCondition(fields)
            for field in self.m_fieldNames:
                sql += field+'=%s '
                
            param = self.m_fieldVals
        
        cnt = dbmngr.dbmngr.execute(sql, param)
        if cnt > 0:
            self.notify('delete')
            self.removeInstance(self)
            
        return cnt
    
    @classmethod
    def quickDelete(cls, fields):
        '''
            不通过数据对象，而是根据字段数值，快速从数据库中删除，浅删除
            fields - {COL_XX:xx,}  不指定id，因为关系表不一定有id
            return - itemCnt affected
        '''
        cls.removeInstance(fields=fields)
        sql = 'delete from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
       
        # 临时增加同步机制，重构后删除
        list = cls.getObjs(fields)
        for obj in list:
            obj.notify('delete')
                
        return dbmngr.dbmngr.execute(sql)
               
    @classmethod
    #def delData(cls, fields=None):
    def delData(cls, fields):
        '''浅删除，从数据库中删除数据
            fields - {COL_XX:xx,}  是否应该指定为id？？？
            return - itemCnt affected
        '''        
        if (fields is None):
            sql = 'delete * from %s' %cls.tbl
        else:
            # 141126 - 删除记录时，同时删除缓存实例，以便同步
            cls.removeInstance(fields=fields)
            sql = 'delete from %s where ' %cls.tbl
            sql += cls._convertSqlCondition(fields)
           
            # 临时增加同步机制，重构后删除
            list = cls.getObjs(fields)
            for obj in list:
                obj.notify('delete')
                
        return dbmngr.dbmngr.execute(sql)
        
        # 上面的代码实际上是自身删除，不会删除无关数据，而定义不规范，
        # 有的子类定义为浅删除，有的定义为深删除（saleorderTemp）
        #list = cls.getObjs(fields)
        #for obj in list:
        #    obj.delete()
        #return len(list)
    
    @classmethod
    def __fields_to_sql(cls, fields):
        
        sql = ''
        for index,val in fields.items():
            field = self.m_fieldNames[index]
            if val is not None:# include:'',0
                sql += field +'=' 
                if (isinstance(val, str) | 
                    isinstance(val, unicode)|
                    isinstance(val, datetime.date)):
                    sql += "'%s'," %(val)
                else:
                    sql += "%s," %(val)
            else:
                sql += field+'=NULL,'

        return sql[:-1]
    
        
    @classmethod
    def update_item(cls, keyVal, fields):
        '''update
        
            fields - {COL_xx:val,}
        '''
        
        sql = 'update %s set %s where id=' %(cls.tbl, cls.__fields_to_sql(fields))
        sql += '%s'
        
        param = (keyVal)
        return dbmngr.dbmngr.execute(sql, param)
            
    def update(self, cols = []):
        '''设置好数据后，执行该函数以便更新数据库
            cols - [COL_xx,]
        '''
        import datetime
        
        self.populateExtra()
        self.notify('update')
        
        if (len(cols) == 0):
            #update all
            cols = [i for i in range(len(self.m_fieldNames))]
        
        sql = 'update  ' + self.tbl + ' set '    
        for i in cols:
            # 最初是只有update非None字段，后来有需要将非None设置成None，所以
            # 该段代码也可以改为
            if (self.m_fieldVals[i] is not None):
                sql += '%s=' %(self.m_fieldNames[i])
                #print type(self.m_fieldVals[i])
                if (isinstance(self.m_fieldVals[i], str) | 
                    isinstance(self.m_fieldVals[i], unicode)|
                    isinstance(self.m_fieldVals[i], datetime.date)):
                    sql += "'%s'," %self.m_fieldVals[i]
                else:
                    sql += '%s,' %(str(self.m_fieldVals[i]))
            elif (self.m_fieldDefVals[i] is None):
                sql += self.m_fieldNames[i]+'=NULL,'
            # 空串会出问题
            #else:
            #    sql += '%s=%s,' %(self.m_fieldNames[i], str(self.m_fieldDefVals[i]))
                    
        sql = sql[:-1] + ' '
        sql += 'where %s=' %(self.m_fieldNames[self.COL_KEY])
        sql += '%s'
        param = (self.m_fieldVals[self.COL_KEY])
        return dbmngr.dbmngr.execute(sql, param)
    
    @classmethod
    def _convertSqlCondition(cls, fields):
        '''fields - {COL_XX:xx,}
            return - '%s=%s and %s=%s...'
        '''
        sql = ''
        for col,val in fields.items():
            sql += '%s=' %(cls.m_fieldNames[col])
            if (isinstance(val, str) | isinstance(val, unicode)):
                sql += "'%s'" %val
            else:
                sql += '%s' %(str(val))
                
            sql += ' and '
            
        return sql[:-5]
        
    @classmethod
    def alterCols(cls, cols):
        cls.uiCols = cols
        
    def __getitem__(self, i):
        return self.getFieldVal(field=self.uiCols[i])
    
    @classmethod
    def fetchFields(cls, filterSql='', fields=None, orderSql=''):
        
        sql = 'select '
        if fields:
            sql += ','.join(fields)
        else:
            sql += ','.join(cls.m_fieldNames)
            
        sql += ' from ' + cls.tbl
        
        if filterSql:
            sql += ' where ' + filterSql
        
        
        sql += ' ' + orderSql
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        result = dbmngr.dbmngr.cursor.fetchall()
        return result
    
    @classmethod
    def fetch(cls, filterSql='', asc=False):
        '''从数据库中获取数据
            return - objList fetched from DB
        '''
        objList = []
        sql = u'select '+ ','.join(cls.m_fieldNames) +' from '+ cls.tbl
        if (filterSql != ''):
            sql += ' where ' + filterSql
        # 140807降序排列
        sql += ' order by %s %s' %(cls.m_fieldNames[cls.COL_KEY], 'asc' if asc else 'desc')
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            print u'fetch None. sql:%s'%sql
            return []
        result = dbmngr.dbmngr.cursor.fetchall()
        for item in result:
            #obj = cls()
            #for j in range(len(item)):
                #obj.m_fieldVals[j] = item[j]
            obj = cls.convertToObj(item)
            #obj = cls.getFirstInstance({0:item[0], 1:item[1]})
            objList.append(obj)
        return   objList
        
        
    @classmethod
    def fetchData(cls, filterSql='', fields=[]):
        '''从数据库中获取数据
            filterSql - string
            fields - [COL_xx,]
            return - objList fetched from DB
        '''
        if (len(fields) == 0):
            sql = 'select '+','.join(cls.m_fieldNames)+' from '+ cls.tbl
        else:
            sql = 'select '
            for i in range(len(fields)):
                sql += '%s,' %cls.m_fieldNames[fields[i]]
           
            sql = sql[:-1] + ' from ' + cls.tbl
            
        if (filterSql != ''):
            sql += ' where ' + filterSql
            
        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt == 0):
##            return []
 
        return dbmngr.dbmngr.cursor.fetchall()
    
    @classmethod
    def refreshDataList(cls):
        '''重新从数据库中获取所有数据'''
        
        if cls in [CDataSaleOrder, CDataPurOrder, CDataSaleDetail, CDataPurDetail]:
            print u'[error]refreshDataList():%s. 取消操作：远程更新大块数据'%cls.tbl
            return
        
        log(TRC, 'refresh %s(avoid repeat)', cls)
        cls.m_dataList = []
        cls.dataLoaded = True
       
        sql = u'select ' + ','.join(cls.m_fieldNames) +' from ' + cls.tbl# + ' order by ' + cls.m_fieldNames[cls.fi_key] + ' desc'
        cnt = dbmngr.dbmngr.execute(sql)
        ret = dbmngr.dbmngr.cursor.fetchall()

        for item in ret:
            #new = createObj(className)
            #new = cls()
            #for j in range(len(item)):
            #    new.m_fieldVals[j] = item[j]
            new = cls.convertToObj(item)
            cls.m_dataList.append(new)
            
        print ('load ', cnt, ' items')
        
    @classmethod
    def convertToObj(cls, vals):
        new = cls()
        for i in range(len(vals)):
            new.m_fieldVals[i] = vals[i]
            
        new.populateExtra()
        return new    
    
    @classmethod
    def generateId(cls, date, newId=0):
        
        if not newId:
            sql = 'select count(*) from %s where id like \'%%%s%%\'' %(cls.tbl, date)
            dbmngr.dbmngr.execute(sql)
            
            cnt = dbmngr.dbmngr.cursor.fetchone()[0]
            print ('count() ', cnt)
            
            newId = cnt+1    
        
        idStr = str(newId)
        idStr = cls.suffix[:-len(idStr)] + idStr
        idStr = '%s-%s-%s' %(cls.prefix, date, idStr[-len(cls.suffix):])
        print ('get new bill id ', cls.tbl, idStr)
        
        #if cls.findObj(idStr):
        #    print ('duplicate id:', idStr)
        #    return cls.generateId(date, newId+1)
        sql = 'select count(*) from %s where id="%s"' %(cls.tbl, idStr)
        dbmngr.dbmngr.execute(sql)
        cnt = dbmngr.dbmngr.cursor.fetchone()[0]
        if cnt>0:
            print ('duplicate id:', idStr)
            return cls.generateId(date, newId+1)
        
        return (newId, idStr)  

        
    @classmethod
    def newAutoIncrId(cls):
        '''产生新的商品ID，假设从1开始
            NOTE：从数据库中查找id，因此要避免同时初始化多个同类对象，会导致id冲突
            NOTE:要注意外键id的约束必须要设置，否则删除后新增的id重复会导致问题
        '''
        sql = 'select id from %s order by id asc' %(cls.tbl)
        
        newId = 1
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return newId
        
        result = dbmngr.dbmngr.cursor.fetchall()
        for row in result:
            if (newId == row[0]):
                newId = row[0] + 1
            else:
                if(row[0] == 0):
                    log(ERR, 'undefined zero id')
                    break
        
        print '***************new id %d********' %newId
        return newId
    
    # 记录软件启动后已经申请的ID数量，避免同时实例化多个变量出现的ID重复申请
    # Note: 子类中应该重新声明该变量，以免被其他子类修改
    billIdCnt = 0
    maxBillId = ''
    @classmethod
    def newBillId(cls, date):
        if (cls.maxBillId == '' or cls.maxBillId[len(cls.prefix)+1:-1-len(cls.suffix)] != date):
            sql = 'select max(id) from %s where id like \'%%%s%%\'' %(cls.tbl, date)
            cnt = dbmngr.dbmngr.execute(sql)
            if (cnt != 0):
                ret = dbmngr.dbmngr.cursor.fetchone()
                #print ('ret ', ret)
                billId = ret[0]
                if (billId is None):
                    newId = 1
                else:
                    newId = int(billId[-len(cls.suffix):]) + 1
            else:
                newId = 1
        else:
            newId = int(cls.maxBillId[-len(cls.suffix):]) + 1
        
        idStr = str(newId)
        idStr = cls.suffix + idStr
        idStr = idStr[-len(cls.suffix):]
        cls.maxBillId = '%s-%s-%s' %(cls.prefix, date, idStr)
        print ('get new bill id ', cls.tbl, cls.maxBillId)
        return cls.maxBillId
    
##        sql = 'select id from %s where id like \'%%%s%%\' order by id desc' %(cls.tbl, date)
##        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt != 0):
##            ret = dbmngr.dbmngr.cursor.fetchone()
##            #print 'ret %s' %ret
##            newId = int(ret[0][-len(cls.suffix):]) + 1
##        else:
##            newId = 1
##        
##        # 140707 - To avoid confict id for mul object    
##        newId += cls.billIdCnt
##        cls.billIdCnt += 1
##        
##        idStr = str(newId)
##        idStr = cls.suffix + idStr
##        idStr = idStr[-len(cls.suffix):]
##        return '%s-%s-%s' %(cls.prefix, date, idStr)

    @classmethod
    def registerFK(cls, srcTable, fields):
        '''计划重构时用来注册外键，这样删除时，自动去删除外键项目，
            避免到srcTable中添加新的删除代码'''
        pass
    
    @classmethod
    def sync_test(cls):
        
        if CDataVersionTable.isOutofDate(cls.tbl):
            cls.refreshDataList()
          
class CDataProduct(CDataModel):
    tbl = u'product'
    listenerList = []
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','name','briefCode','categoryId','brand','spec','onUse',
                    'unit1','ratio1','onhand1','defCost1',
                    'defSale1','lastPur1','minPur1','maxPur1',
                    'unit2','ratio2','onhand2','defCost2',
                    'defSale2','lastPur2','minPur2','maxPur2',
                    'unit3','ratio3','onhand3','defCost3',
                    'defSale3','lastPur3','minPur3','maxPur3',
                    'memo', 'manualNum']
    m_fieldCNNames = ['id',u'名称',u'简码',u'类型',u'品牌',u'规格',u'启用',
                    u'单位',u'关系',u'在手',u'预设成本',
                    u'预设售价',u'最近进价',u'最低进价',u'最高进价',
                    'unit2','ratio2','onhand2','defCost2',
                    'defSale2','lastPur2','minPur2','maxPur2',
                    'unit3','ratio3','onhand3','defCost3',
                    'defSale3','lastPur3','minPur3','maxPur3',
                    'memo', 'manualNum']
    #autoId = True
    m_fieldDefVals = []
    
    [COL_ID, COL_NAME, COL_BRIEFCODE,COL_CATEGORYID,COL_BRAND,COL_SPEC,COL_ONUSE, 
    COL_UNIT1,COL_RATIO1,COL_ONHAND1,COL_DEFCOST1,COL_DEFSALE1,
    COL_LASTPUR1,COL_MINPUR1,COL_MAXPUR1,
    COL_UNIT2,COL_RATIO2,COL_ONHAND2,COL_DEFCOST2,COL_DEFSALE2,
    COL_LASTPUR2,COL_MINPUR2,COL_MAXPUR2,
    COL_UNIT3,COL_RATIO3,COL_ONHAND3,COL_DEFCOST3,COL_DEFSALE3,
    COL_LASTPUR3,COL_MINPUR3,COL_MAXPUR3, 
    COL_MEMO, COL_MANUALNUM] = [i for i in range(33)]
    
    UNIT_CNT = 3
    UNIT_FIELD_CNT = 8
    
    def __init__(self):
        CDataModel.__init__(self)
        self.m_fieldDefVals = [-1,'','',1,'','',1,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None]
                        
        self.m_fieldVals = [-1,'','',1,'','',1,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None]

##    @classmethod
##    def createNew(cls):
##        '''工厂方法 - 主要目的是封装id分配方式'''
##        obj = cls()
##        
##        obj.m_fieldVals = [-1,'','',1,'','',1,
##                        '',None,None,None,None,None,None,None,
##                        '',None,None,None,None,None,None,None,
##                        '',None,None,None,None,None,None,None,
##                        '']
##        
##        obj.setValue(cls.COL_ID, cls.newId())
##        return obj
    
    def validUnit(self, iUnit):
        return self.getValue(iUnit) and self.getValue(iUnit) != ''
        
    def getUnratioUnit(self, unit):
        if not self.validUnit(self.COL_UNIT2):
            return []
        
        ratioUnits = []
        unratioUnits = []
        for i in range(3):
            iUnit = self.COL_UNIT1 + i * self.UNIT_FIELD_CNT
            if self.validUnit(iUnit):
                iRatio = iUnit + 1
                if self.getValue(iRatio) is None or self.getValue(iRatio) == '':
                    unratioUnits.append(self.getValue(iUnit))
                else:
                    ratioUnits.append(self.getValue(iUnit))
            else:
                break
        
        print(ratioUnits, unratioUnits)
        
        if unit not in ratioUnits:
            unratioUnits.remove(unit)
            if ratioUnits:
                unratioUnits.append(ratioUnits[0])
                
        return unratioUnits
                
    def getUnitDict(self):
        dict = {}
        dict[self.m_fieldVals[self.COL_UNIT1]] = self.m_fieldVals[self.COL_UNIT1+1:self.COL_MAXPUR1+1]
        if self.m_fieldVals[self.COL_UNIT2] and self.m_fieldVals[self.COL_UNIT2] != '':
            dict[self.m_fieldVals[self.COL_UNIT2]] = self.m_fieldVals[self.COL_UNIT2+1:self.COL_MAXPUR2+1]
        
        if self.m_fieldVals[self.COL_UNIT3] and self.m_fieldVals[self.COL_UNIT3] != '':
            dict[self.m_fieldVals[self.COL_UNIT3]] = self.m_fieldVals[self.COL_UNIT3+1:self.COL_MAXPUR3+1]
            
        return dict
    
    def validRatio(self, ratio):
        return ratio is not None and ratio != ''
        
    def syncUnitVal(self, baseLine, num):
        '''根据某一行的值，重新设置其他存在数量关系行的数据'''
        iRatio = self.COL_RATIO1 + baseLine * self.UNIT_FIELD_CNT
        b_ratio = self.getValue(iRatio)              
        if(self.validRatio(b_ratio)):
            # 不再使用变动值而直接使用在手数量
            num = self.getValue(self.COL_ONHAND1 + baseLine * self.UNIT_FIELD_CNT)
        
            #更新其他
            print u'TODO:产品数量关系更新'
            for i in range(self.UNIT_CNT):
                if i == baseLine:
                    continue
                iRatio = self.COL_RATIO1 + i * self.UNIT_FIELD_CNT
                i_ratio = self.getValue(iRatio)
                if (self.validRatio(i_ratio)):
                    iOnhand = self.COL_ONHAND1 + i * self.UNIT_FIELD_CNT
                    i_num = num * i_ratio / b_ratio
                    #self.setValue(iOnhand, i_num + self.getValue(iOnhand))
                    self.setValue(iOnhand, i_num)
    
    def setUnitData(self, unitVals):
        for i in range(len(unitVals)):
            self.m_fieldVals[self.COL_UNIT1+i] = unitVals[i]
            
        if (i != 23):
            log(ERR, 'Incorrect index %d', i)
    
    @classmethod
    def updateOnhand(cls, proId, unit, num):
        '''销售时更新在手数量'''
        obj = cls.getFirstInstance({cls.COL_ID:proId})
        for i in range(cls.UNIT_CNT):
            iUnit = cls.COL_UNIT1 + i * cls.UNIT_FIELD_CNT
            if(unit == obj.getValue(iUnit)):
                iOnhand = cls.COL_ONHAND1 + i * cls.UNIT_FIELD_CNT
                
                num += obj.getValue(iOnhand)
                if num < 0:
                    # 这里应该根据数据库settings设置来操作，暂时默认为2
                    num = 0
                    
                obj.setValue(iOnhand, num)                
                obj.syncUnitVal(i, num)            
                obj.update()
                return
    
    def calcAvgCost(self, unit, onhand):
        pass
        
    @classmethod
    def updatePurchase(cls, proId, unit, price, num):
        '''采购订单更新采购价格、数量'''
        obj = cls.getFirstInstance({cls.COL_ID:proId})
        
        for i in range(cls.UNIT_CNT):
            iUnit = cls.COL_UNIT1 + i * cls.UNIT_FIELD_CNT
            if(unit == obj.getValue(iUnit)):
                iRatio = cls.COL_RATIO1 + i * cls.UNIT_FIELD_CNT
                iOnhand = cls.COL_ONHAND1 + i * cls.UNIT_FIELD_CNT
                iCost = cls.COL_DEFCOST1 + i * cls.UNIT_FIELD_CNT
                iSale = cls.COL_DEFSALE1 + i * cls.UNIT_FIELD_CNT
                iLastPrice = cls.COL_LASTPUR1 + i * cls.UNIT_FIELD_CNT
                iMinPur = cls.COL_MINPUR1 + i * cls.UNIT_FIELD_CNT
                iMaxPur = cls.COL_MAXPUR1 + i * cls.UNIT_FIELD_CNT
                
                obj.setValue(iOnhand, num + obj.getValue(iOnhand))
                if(obj.getValue(iCost) == 0.0):
                   # 浮点比较？？
                   obj. setValue(iCost, price*1.05)
                   
                if(obj.getValue(iSale) == 0.0):
                   # 浮点比较？？
                   obj. setValue(iSale, price*1.2)
                      
                obj.setValue(iLastPrice, price)
                
                if(price < obj.getValue(iMinPur) or obj.getValue(iMinPur) == 0.0):
                    obj.setValue(iMinPur, price)
                    
                if(price > obj.getValue(iMaxPur) or obj.getValue(iMaxPur) == 0.0):
                    obj.setValue(iMaxPur, price)
                        
                obj.syncUnitVal(i, num)
                
        obj.update()
               
    @classmethod
    def newId(cls):
        '''产生新的商品ID，假设数据库编号从1开始，从0开始会出错'''
        sql = 'select id from %s order by id asc' %(cls.tbl)
        
        newId = 1
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return newId
        
        result = dbmngr.dbmngr.cursor.fetchall()
        for row in result:
            #print '%d %d' %(newId, row[0])
            if (newId == row[0]):
                newId += 1
            else:
                break
                        
        return newId
    
    def isDeletable(self):
        return False
    
    def isOnuse(self):
        if (self.getValue(self.COL_ONUSE) != 1):
            return False
        return True
    
    def enable(self, enable=True):
        if (enable):
            self.setValue(self.COL_ONUSE, 1)
        else:
            self.setValue(self.COL_ONUSE, 0)
        self.update()
            
    def attachLabel(self, labelId):
        obj = CDataProLabel.factory()
        fields = {CDataProLabel.COL_PROID:self.m_fieldVals[self.COL_ID],
                    CDataProLabel.COL_LABELID:labelId}
        obj.setMulFieldVal(fields)
        obj.insert()
    
    def getPricePur(self, unit=u''):
        if not unit:
            return self.getValue(self.COL_LASTPUR1)
        
        if unit == self.getValue(self.COL_UNIT1):
            return self.getValue(self.COL_LASTPUR1)
        elif unit == self.getValue(self.COL_UNIT2):
            return self.getValue(self.COL_LASTPUR2)
        elif unit == self.getValue(self.COL_UNIT3):
            return self.getValue(self.COL_LASTPUR3)
        else:
            print u'未知单位:%s'%unit
            
        return 0.0
    
    @classmethod    
    def getManualNum(cls, proId):
        proObj = cls.findObj(proId)
        return proObj.getValue(proObj.COL_MANUALNUM)
        
                   
class CDataCategory(CDataModel):
    tbl = u'category'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id' ,'name', 'parentId']
    m_fieldCNNames = []
    m_fieldDefVals = [-1,'','']
    autoId = True
    
    [COL_ID, COL_NAME, COL_PARENT] = [i for i in range(3)]
    
    @classmethod
    def getName(cls, id):
        return cls.getFirstFieldVal({'id':id}, 'name')

    @classmethod
    def getDefCategoryId(cls):
        return 1
    
    @classmethod
    def getDefaultObj(cls):
        id = cls.getDefCategoryId()
        
        return cls.getFirstInstance({cls.COL_ID:id})
        
    @classmethod
    def getChildId(cls, pid):
        child = [pid]
        
        for id in child:
            filter = 'parentId=%d' %id
            objList = CDataCategory.fetch(filter)
            for obj in objList:
                child.append(obj.getFieldVal(index=CDataCategory.COL_ID))
        
        log(TRC, 'child of %d is %s', (pid, child))
        return child
    
##    def __init__(self):    
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * 3
 
class CDataKeyword(CDataModel):
    tbl = u'keyword'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'name']
    m_fieldCNNames = []
    m_fieldDefVals = [-1, '']
    #autoId = True
    
    [COL_ID, COL_NAME] = [i for i in range(2)]
    
    @classmethod
    def add(cls, name):
        obj = cls.factory()
        obj.setValue(cls.COL_NAME, name)
        cnt = obj.insert()
        if (cnt <= 0):
            obj = cls.find(name)
        return obj.getValue(cls.COL_ID) if obj else -1
    
    @classmethod
    def find(cls, name):
        return cls.getFirstInstance({cls.COL_NAME:name})
    
    @classmethod
    def parseStr(cls, objList):
        str = ''
        for obj in objList:
            str += '%s ' %obj.getFieldVal(index=CDataKeyword.COL_NAME)
        return str
        
##    def __init__(self):    
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * 2

class CDataProLabel(CDataModel):
    '''关系型数据表，描述商品与关键字对应关系'''
    tbl = u'proLabeling'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['proId', 'keywordId']
    m_fieldCNNames = []
    m_fieldDefVals = ['','']
    fi_key = -1
    [COL_PROID, COL_LABELID] = [i for i in range(2)]
    
    pro_label = {} #{proId:set([labelId...]), }
    label_pro = {} #{labelId:set([proId...], }
    
    @classmethod
    def populateClsData(cls):
        for obj in cls.getObjList():
            proId = obj.getValue(obj.COL_PROID)
            labelId = obj.getValue(obj.COL_LABELID)
            
            if not cls.pro_label.has_key(proId):
                cls.pro_label[proId] = set()
            
            #if labelId not in cls.pro_label[proId]:
            cls.pro_label[proId].add(labelId)
                
            if not cls.label_pro.has_key(labelId):
                cls.label_pro[labelId] = set()
                
            #if proId not in cls.label_pro[labelId]:
            cls.label_pro[labelId].add(proId)
        
    @classmethod
    def getProId(cls, labelIdList, bAnd=True):
        '''bAnd  - True 符合全部label的pro
                - False 只要符合labels之一的pro'''

        proIdset = set()
        if (bAnd):
            for labelId in labelIdList:
                if proIdset:
                    proIdset &= cls.label_pro[labelId]
                else:
                    proIdset = cls.label_pro[labelId]
        else:
            for labelId in labelIdList:
                if proIdset:
                    proIdset |= cls.label_pro[labelId]
                else:
                    proIdset = cls.label_pro[labelId]
            
        return list(proIdset)
##            for obj in cls.getObjList():
##                if obj.getValue(obj.COL_LABELID) in labelIdList:
##                    if not obj.getValue(obj.COL_PROID) in proIds
##                        proIds.append(obj.getValue(obj.COL_PROID))
##            sql = 'keywordId in (' + self.concat(keywordIds, ',') +')'
##            proLabelObjs = CDataProLabel.fetch(sql)
##            for obj in proLabelObjs:
##                proIds.append(obj.getValue(CDataProLabel.COL_PROID))
                
        return proIds
        
    @classmethod
    def getKeywordObjList(cls, proId):
        '''fetch keyword obj list by product'''
        #
        sql = '%s=%d' %(cls.m_fieldNames[0], proId)
        data = cls.fetchData(sql, [cls.COL_LABELID])
        if (len(data) == 0):
            return []
        
        #data is tuple's tuple ((),) -->(,)
        dataList = []
        for item in data:
            dataList.append(str(item[0]))
        sql = 'id in (' 
        sql += ','.join(dataList)  #long element
        #sql += str(tuple(dataList)) #[1] -> (1,)
        sql += ')'
        return CDataKeyword.fetch(sql)
      

class CDataCustomer(CDataModel):
    tbl = u'customer'
    m_dataList = []
    m_fieldNo = {}
##    m_fieldNames = ['id','name','briefCode','addr','email',
##                    'fax','linkman','lm_phone',
##                    'lm_memo','debt','memo']
    m_fieldCNNames = [u'客户编号',u'客户名称',u'助记码',u'联系地址',u'电子邮件',
                    u'传真',u'备注',u'联系人',u'联系人电话',
                    u'联系人备注',u'应收货款', u'', u'']
    
    listenerList = []
    m_fieldDefVals = []
##    [COL_ID,COL_NAME,COL_BREIFCODE,COL_ADDR, COL_EMAIL,COL_FAX,
##    COL_LINKMAN,COL_LMPHONE,COL_LMMEMO,COL_DEBT,COL_MEMO] = [i for i in range(11)]
    
##    def __init__(self):    
##        CDataModel.__init__(self)
        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
        #除非在数据模型中存在的全部是string类型
        #140704 新增客户时，初始化其id，以便其他步骤取用
        #       newAutoIncrId()从数据库中查找id，因此要避免同时初始化多个同类对象
        #       会导致id冲突
        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
        #        所以为之新增函数
        #id = self.newAutoIncrId()
       
        #self.m_fieldVals = [-1, '','','','','','','','','','',1,'','','']
##        self.m_fieldVals = copy.deepcopy(self.m_fieldDefVals)
               
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldDefVals = []
        cls.COL_ID = cls.appendField('id', defVal=-1)
        cls.COL_NAME = cls.appendField('name')
        cls.COL_BREIFCODE = cls.appendField('briefCode')
        cls.COL_ADDR = cls.appendField('addr')
        cls.COL_EMAIL = cls.appendField('email')
        cls.COL_FAX = cls.appendField('fax')
        cls.COL_LINKMAN = cls.appendField('linkman')
        cls.COL_LMPHONE = cls.appendField('lm_phone')
        cls.COL_LMMEMO = cls.appendField('lm_memo')
        cls.COL_DEBT = cls.appendField('debt')
        cls.COL_MEMO = cls.appendField('memo')
        cls.COL_REGION = cls.appendField('region', defVal=1)
        cls.COL_TYPE = cls.appendField('type')
        cls.COL_PRINTNAME = cls.appendField('printName')
        cls.COL_LASTORDER = cls.appendField('lastOrder', defVal=None)
        
        cls.refreshDataList()
        
        CDataVoucher.attach(cls.updateExtraByVoucher)
        CDataSaleOrder.attach(cls.updateExtraByOrder)
    
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
    
    def populateExtra(self):
        #获取欠款情况：应收货款总金额-已收货款总金额+期初欠款
        self.debt = self.getSaleAmount() - self.getPaidAmount() + self.m_fieldVals[self.COL_DEBT]
        if abs(self.debt) < 0.01:
            self.debt = 0.0
        self.overDue = False
    
    @classmethod
    def updateExtraByVoucher(cls, op, voucher):
        #没必要立刻更新，只需要overDue，然后使用时更新即可
        id = voucher.getValue(voucher.COL_PEERID)
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        # 不必再进行notify，一则防止递归，二则没必要（因为过时的是extraData，
        # 除非是notify界面，但是现在还没有设计）
        #obj.notify()
    
    @classmethod    
    def updateExtraByOrder(cls, op, order):
        id = order.getValue(order.COL_PEERID)
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        #obj.notify()
                
    def getDebt(self):
        if self.overDue:
            self.populateExtra()
        return self.debt
    
    def getInitDebt(self):
        return self.m_fieldVals[self.COL_DEBT]
    
    def getSaleAmount(self):
        custId = self.getValue(self.COL_ID)
        return CDataSaleOrder.getCustSaleAmount(custId)
    
    def getPaidAmount(self):
        custId = self.getValue(self.COL_ID)
        return CDataVoucher.getCustPaid(custId)
    
    @classmethod
    def getProDetail(cls, custId):
        return CDataSaleDetail.getCustomerExpense(custId)
        
    def getRegion(self):
        regionId = self.getValue(self.COL_REGION)
        regionObj = CDataCustomerRegion.getFirstInstance({CDataCustomerRegion.fi_id:regionId})
        return regionObj.getValue(regionObj.fi_name)
            
    @classmethod
    def getSearchSql(cls, val):
        str = "'%%%s%%'"  %val
        sql = ''
        # 'lastOrder'是Date类型，like报错1271
        for field in CDataCustomer.m_fieldNames:
            if (field == 'lastOrder'):
                continue
            sql += '%s like %s or ' %(field, str)
        return sql[:-4]
    
    @classmethod
    def getStat(cls): 

        sql = '''SELECT name, 
                        sum(initdebt),
                        sum(total), 
                        sum(discount), 
                        sum(paid),
                        sum(initdebt)+sum(total)-sum(discount)-sum(paid),
                        sum(cnt),
                        lastOrder
                from ( 
                    select customer.name as name,                    
                           0 as initdebt,
                           sum(saleorder.total) as total,
                           sum(discount) as discount,
                           count(*) as cnt,
                           0 as paid,
                           customer.lastOrder as lastOrder
                    from customer,saleOrder
                    where customer.id=saleOrder.peerId
                    group by customer.id

                    union 

                    select customer.name as name,                    
                           customer.debt as initdebt,
                           0.0 as total,
                           0.0 as discount,
                           0 as cnt,
                           sum(voucher.money) as paid,
                           customer.lastOrder as lastOrder
                    from customer,voucher
                    where customer.id=voucher.peerId
                    group by customer.id) t
                group by name'''
    
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def hasUnalloctVoucher(cls):
        '''检查是否有未分配的收款单'''
        pass
        
                   
class CDataCustomerContact(CDataModel):
    tbl = 'customer_contact'
    m_dataList = []
    m_fieldDefVals = []
##    def __init__(self):
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.iId = cls.appendField('id', u'编号')
        cls.iPeerId = cls.appendField('peerId')
        cls.iName = cls.appendField('name', u'联系人名称')
        cls.iPhone = cls.appendField('phone', u'联系人电话')
        cls.iMemo = cls.appendField('memo', u'信息备注')
        
        cls.refreshDataList()
       
    def getText(self):
        name = self.getValue(self.iName)
        phone = self.getValue(self.iPhone)
        if(phone != ''):
            str = name + ',' + phone
        else:
            str = name 
        return str
    
    @classmethod
    def getContactText(cls, peerId):
        data = []
        for obj in cls.m_dataList:
            if(peerId == obj.getValue(cls.iPeerId)):
                data.append(obj.getText())
        return data

class CDataCustomerLabel(CDataModel):
    tbl = 'customer_label'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')
        
        cls.refreshDataList()
        
    @classmethod
    def getNames(cls):
        ret = []
        for obj in cls.m_dataList:
            ret.append(obj.getValue(obj.fi_name))
            
        return ret
        
    @classmethod
    def add(cls, name):
        obj = cls.factory()
        obj.setValue(cls.fi_name, name)
        return obj.insert()
        
class CDataCustomerLabeling(CDataModel):
    tbl = 'customer_labeling'
    m_dataList = []
    fi_key = -1
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_custId = cls.appendField('custId')
        cls.fi_labelId = cls.appendField('labelId')
        
        #cls.refreshDataList()
    
    @classmethod
    def getKeyword(cls, custId):
        sql = 'select name from %s where id in (select labelId from %s where custId=%d)' \
                %(CDataCustomerLabel.tbl, cls.tbl, custId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        
        result = dbmngr.dbmngr.cursor.fetchall() 
        list = []
        for item in result:
            list.append(item[0])
        return list
    
class CDataCustomerRegion(CDataModel):
    tbl = 'customer_region'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'地区名称')
        cls.fi_code = cls.appendField('code', u'简码')
        cls.fi_memo = cls.appendField('memo', u'说明')
        
        cls.refreshDataList()
    
    @classmethod
    def getName(cls, id):
        obj = cls.findObj(id)
        return obj.getValue(obj.fi_name) 
        
##    @classmethod
##    def createNew(cls):
##        obj = cls()
##        obj.setValue(cls.fi_id, cls.newAutoIncrId())
##        return obj
   
    @classmethod
    def add(cls, name, memo, code=''):
        import config
        obj = cls.factory()
        obj.setValue(cls.fi_name, name)
        if not code:
            code = config.CBriefCode.getBriefCode(name)
        obj.setValue(cls.fi_code, code)
        obj.setValue(cls.fi_memo, memo)
        return obj.insert()
    
    @classmethod        
    def getDetault(cls):
        return cls.getFirstInstance({cls.fi_id:1})
        
class CDataCustomerType(CDataModel):
    tbl = 'customer_type'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'类型名称')
        
        cls.refreshDataList()
    
##    @classmethod
##    def createNew(cls):
##        obj = cls()
##        obj.setValue(cls.fi_id, cls.newAutoIncrId())
##        return obj
    
    @classmethod
    def add(cls, name):
        obj = cls.factory()
        obj.setValue(cls.fi_name, name)
        return obj.insert()

class CDataSaleDetail(CDataModel):
    tbl = 'saleDetails'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['detail_id', 'billId', 'peerId','proId','proName','proSpec','unit','price', 'num', 'total', 'memo']
    m_fieldCNNames = ['detail_id','saleId', 'custId', 'proId','proName','proSpec','unit', 'price', 'num', 'total', 'memo']
    [COL_DETAILID, COL_BILLID,COL_PEERID,COL_PROID,COL_PRONAME,COL_PROSPEC,COL_UNIT,COL_PRICE,
    COL_NUM,COL_TOTAL, COL_MEMO] = [i for i in range(11)]
    fi_key = 0
    m_fieldDefVals = ['']*11
    
    def __init__(self):
        CDataModel.__init__(self)
        self.m_fieldVals = [''] * len(self.m_fieldNames)

    @classmethod
    def getProDetail(cls, billId):
        '''获取订单销售产品列表
            return - [[id,name,unit,cnt,price,total],[...],...]
        '''
        sql = "select proId,proName,proSpec,unit,num,price,total,memo "\
                "from %s "\
                "where billId='%s'" %(cls.tbl, billId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getCustomerExpense(cls, peerId):
        data = []
        stat = cls.getStat(peerId)
        for proId,cnt,amount in stat:
            #如果有统计，则必有消费,[0]为最近订单信息
            # 150430 - 需要增加处理多单位的情况
            #proName,unit,price,billId,num,total = cls.getProSaleInfo(proId,peerId)[0]
            #data.append([billId,proId,proName,unit,price,cnt,amount])
            result = cls.getProSaleInfo(proId,peerId)
            maxBillId = result[0][3]
            for proName,unit,price,billId,num,total,memo in result:
                if billId == maxBillId:
                    data.append([billId,proId,proName,unit,price,cnt,amount])
        
        return data    
        
    @classmethod
    def getStat(cls, peerId):
##        sql = "select distinct proId,proName,unit,price "\
##                "from %s " \
##                "where custId=%s order by saleId desc" %(cls.tbl, custId)
##        sql = "select first(proId),proName,unit,price "\
##                "from %s " \
##                "where custId=%s order by saleId desc" %(cls.tbl, custId)
        # 该SQL得到的price有问题，简单起见目前把pro销售统计与近期消费分开
        # 作为两条sql
##        sql = "select max(saleId),proId,proName,unit,price,sum(num),sum(total) "\
##                "from %s " \
##                "where custId=%s gruop by proId" %(cls.tbl, custId)

        sql = "select proId,sum(num),sum(total) "\
                "from %s " \
                "where peerId=%s group by proId" %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getProSaleInfo(cls, proId, peerId=-1):
        if(peerId != -1):
            resultStr = 'proName,unit,price,billId,num,total,memo'
            filterStr = 'proId=%s and peerId=%s' %(proId, peerId)
        else:
            resultStr = 'proName,unit,price,billId,num,total,peerId,memo'
            filterStr = 'proId=%s' %proId
            
        sql = "select %s "\
                "from %s " \
                "where %s order by billId desc limit 50" %(resultStr, cls.tbl, filterStr)    
         
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    def incrInventory(self, incr=True):
        proId = self.getValue(self.COL_PROID)
        unit = self.getValue(self.COL_UNIT)
        num = self.getValue(self.COL_NUM)
        
        if not(incr):
            num = -num
        CDataProduct.updateOnhand(proId, unit, num)
    
    def decrInventory(self):
        self.incrInventory(False)
            
    def insert(self):
        self.incrInventory(False)
        return super(CDataSaleDetail, self).insert_with_autoid()
        
    def delete(self):
        self.incrInventory()
        return super(CDataSaleDetail, self).delete()
            
    @classmethod
    def delData(cls, fields):
        '''从数据库中删除数据，需要先修改相关产品库存
            fields - {COL_XX:xx,}
            return - itemCnt affected
        '''
        cls.adjustInventory(fields)
##        sql = 'delete from %s where ' %cls.tbl
##        sql += cls._convertSqlCondition(fields)
##            
##        return dbmngr.dbmngr.execute(sql)
    
        return super(CDataSaleDetail, cls).delete()

    
    @classmethod
    def adjustInventory(cls, fields, incr=True):
        sql = 'select proId,unit,num from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
        
        cnt = dbmngr.dbmngr.execute(sql)
        result = dbmngr.dbmngr.cursor.fetchall()
        for proId,unit,num in result:
            if not(incr):
                num = -num
            CDataProduct.updateOnhand(proId, unit, num)

class CDataSaleDetailTemp(CDataSaleDetail):
    tbl = 'saleDetails_temp'
    m_dataList = []
    fi_key = -1
    
##    def insert(self):
##        #self.incrInventory(False)
##        return super(CDataSaleDetailTemp, self).insert()
##        
##    def delete(self):
##        #self.incrInventory()
##        return super(CDataSaleDetailTemp, self).delete()

class CDataOrderState(CDataModel):
    tbl = 'order_state'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'状态名称')
        cls.refreshDataList()
    
    @classmethod
    def add(cls, name):
        if name == '':
            return
        cls.quickInsert({cls.fi_name:name})   
        
                
class CDataSaleOrder(CDataModel):
    tbl = 'saleOrder'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    clsDetail = CDataSaleDetail
    listenerList = []
    
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'peerId', 'billDate', 'total', 
    #                'discount', 'handlerId', 'recorderId', 'dlvyAddr', 'memo', 'buyer']
    #m_fieldCNNames = [u'编号', 'custId',  'billDate', 'total', 'paid',
    #                'unpaid', 'discount', 'handlerId', 'recorderId','dlvyId', 'memo', 'buyer']
    
    #[COL_ID,COL_PEERID,COL_BILLDATE,COL_TOTAL,COL_PAID,COL_UNPAID,
    #COL_DISCOUNT,COL_HANDLERID,COL_RECOREDERID,COL_DLVYADDR,COL_MEMO,COL_BUYER] = [i for i in range(12)]
    
    #clsDetail = CDataSaleDetail
    
##    def __init__(self):
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    autoId = False
    prefix = 'XS'
    suffix = '00000'
    maxBillId = ''
    #记录已经申请的ID数量，避免同时实例化多个变量出现的ID重复申请
    #Note: 子类中应该重新声明该变量，以免被其他子类修改
    billIdCnt = 0
    
    @classmethod
    def newId_obs(cls, date):
        # 使用CDataModel.generateId替代了该方法，在保存之前生产ID
        maxid = max(CDataSaleOrder.newBillId(date), CDataSaleOrderTemp.newBillId(date))
        maxid = maxid[:-4]+'0'+maxid[-3:]
        return maxid
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.COL_ID = cls.appendField('id')
        cls.COL_PEERID = cls.appendField('peerId')
        cls.COL_BILLDATE = cls.appendField('billDate')
        #cls.COL_WHID = cls.appendField('warehouseId')
        cls.COL_TOTAL = cls.appendField('total')
        #cls.COL_PAID = cls.appendField('paid')
        cls.COL_DISCOUNT = cls.appendField('discount')
        cls.COL_HANDLERID = cls.appendField('handlerId')
        cls.COL_RECOREDERID = cls.appendField('recorderId')
        cls.COL_DLVYADDR = cls.appendField('dlvyAddr')
        cls.COL_MEMO = cls.appendField('memo')
        cls.COL_BUYER = cls.appendField('buyer')
        cls.COL_STATE = cls.appendField('state')
        cls.COL_DLVYID = cls.appendField('dlvyId', defVal=None)
        cls.COL_DELIVERYPLAN = cls.appendField('deliveryPlan', defVal=None)        
        cls.COL_PRINTED = cls.appendField('printed', defVal=0)
        
        cls.COL_CREATETIME = cls.appendField('create_time', defVal=None)
        cls.COL_UPDATER = cls.appendField('updater', defVal=None)
        cls.COL_UPDATETIME = cls.appendField('update_time', defVal=None)
        cls.COL_UPDATECNT = cls.appendField('update_cnt', defVal=0)
        
        cls.COL_AUDIT = cls.appendField('audit', defVal=0)
        cls.COL_AUDITOR = cls.appendField('auditor', defVal=None)
        cls.COL_AUDITTIME = cls.appendField('audit_time', defVal=None)
        cls.COL_ANTIAUDITOR = cls.appendField('antiauditor', defVal=None)
        cls.COL_ANTIAUDITTIME = cls.appendField('antiaudit_time', defVal=None)
        #cls.refreshDataList()
                
        #cls.populateDataList()
        
        CRelFundCustomer.attach(cls.updateExtra)
    
    def setAudit(self, audit=1):
        self.setValue(self.COL_AUDIT, audit)
        self.update()
        
    def isAudited(self):
        return (self.getValue(self.COL_AUDIT) == 1)
        
    def isPrinted(self):
        return self.getValue(self.COL_PRINTED)
    
    def setPrinted(self):
        self.setValue(self.COL_PRINTED, 1)
        self.update()
    
    @classmethod
    def updateExtra(cls, op, fund):
        # 还会notify CDataSaleOrderTemp，不过没什么影响，暂时忽略
        #没必要立刻更新，只需要overDue，然后使用时更新即可
        orderId = fund.getValue(fund.COL_BILLID)
        print ('notify ', cls, op, orderId)
        obj = cls.findObj(orderId)
        if obj: # if for orderTemp
            obj.overDue = True
            obj.notify()
                
    @classmethod
    def populateDataList(cls):
        # 除数据库字段外，初始化时需要载入的数据（需要合成，以便UI可以快速反应）
        #index = len(cls.m_fieldNames)
        #cls.COL_PAID = index
        #cls.COL_UNPAID = index + 1
        
        for obj in cls.getObjList():
            obj.populateExtra()
    
    def populateExtra(self):
        self.paid = CRelFundCustomer.getPaidAmount(self.m_fieldVals[self.COL_ID])
        self.overDue = False
    
    def getPeerName(self):
        peerId = self.getValue(self.COL_PEERID)
        return CDataCustomer.findObj(peerId).getValue(CDataCustomer.COL_NAME)
    
    def getPeerObj(self):
        '''重构可以用字典优化效率'''
        peerId = self.getValue(self.COL_PEERID)
        return CDataCustomer.findObj(peerId)
    
    def getDeliveryPlan(self):
        plan = self.getValue(self.COL_DELIVERYPLAN)
        if plan:
            return plan
        else:
            return u'' 
       
    def getPaid(self):
        if self.overDue:
            self.populateExtra()
        return self.paid
    
    def getUnpaid(self):
        total = self.m_fieldVals[self.COL_TOTAL]
        discount = self.m_fieldVals[self.COL_DISCOUNT]
        unpaid = total - discount - self.getPaid()
        return unpaid
    
    def getDlvyAddr(self):
        dlvyId = self.getValue(self.COL_DLVYID)
        if (dlvyId is None):
            return u''
        
        dlvyObj = CDataDelivery.findObj(dlvyId)
        return dlvyObj.getValue(dlvyObj.COL_NAME)
        
    @classmethod
    def getBillAmount(cls, peerId):
        '''
            return - [billId,amount,paid,discount,unpaid]
        '''
    
##        #sql = 'select id,total,paid,discount,unpaid '
##        sql = 'select id,total,discount '
##        sql += 'from %s where peerId=%d order by id desc' %(cls.tbl, peerId)
##                
##        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt > 0):
##            return dbmngr.dbmngr.cursor.fetchall()
##        else:
##            return []
        
        #list = cls.getObjs({cls.COL_PEERID:peerId})
        list = cls.fetch('peerId="%d"'%peerId)
        data = []
        for obj in list:
            data.append([obj.getValue(obj.COL_ID),
                        obj.getValue(obj.COL_TOTAL),
                        obj.getPaid(),
                        obj.getValue(obj.COL_DISCOUNT),
                        obj.getUnpaid()])
        return data
        
    @classmethod
    def getProDetail(cls, billId):
        '''return - [[id,name,unit,cnt,price,total],[...]] 与界面显示保持一致'''
        return cls.clsDetail.getProDetail(billId)
    
##    def delete(self):
##        return super(CDataSaleOrder, self).delete(True)
    
    @classmethod
    def deleteRefItem(cls, obj):
        
        # CRelFundCustomer
        id = obj.getValue(obj.COL_ID)
        CRelFundCustomer.quickDelete({CRelFundCustomer.COL_BILLID:id})
        
        # CDataSaleDetail
        CDataSaleDetail.quickDelete({CDataSaleDetail.COL_BILLID:id})

    @classmethod
    def clearDetail(cls, id):
        CDataSaleDetail.quickDelete({CDataSaleDetail.COL_BILLID:id})
    
    @classmethod
    def deleteBill(cls, billId, delFund=True):
        '''删除订单及订单明细'''
        # 添加收款单后，还需删除相关收款记录
        # 编辑订单时，如果id没有变化，则不需要解除收款关系
        if delFund:
            CRelFundCustomer.quickDelete({CRelFundCustomer.COL_BILLID:billId})
        CDataSaleDetail.quickDelete({CDataSaleDetail.COL_BILLID:billId})
        cls.quickDelete({cls.COL_ID:billId})        
    
    
        
    @classmethod
    def getCustomerDebt(cls, custId):
        sql = 'select sum(total),sum(paid),sum(unpaid),sum(discount) from %s where peerId=%s' %(cls.tbl,custId)
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
##        if (cnt != 0):
##            ret = dbmngr.dbmngr.cursor.fetchone()
##            print ret
##            return ret[0]
##        else:
##            return (0,0,0,0)
        #Maybe for sum() was used, there will be return for sure,
        #(None,None,None,None)will be returned if the custid unfound
        ret =  dbmngr.dbmngr.cursor.fetchone()
        if(ret is None):
            ret = (0.0,0.0,0.0,0.0)
        return ret
    
    @classmethod
    def getCustSaleAmount(cls, custId):
        '''获取应收总额（扣减优惠额之后的）'''
        sql = 'select sum(total),sum(discount) from %s where peerId=%s' %(cls.tbl,custId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None,None,None,None)will be returned if the custid unfound
        ret =  dbmngr.dbmngr.cursor.fetchone()
        if(ret[0] is None):
            ret = (0.0, 0.0)
        return ret[0] - ret[1]
    
##    def collect(self, money):
##        self.setValue(self.COL_PAID, self.getValue(self.COL_PAID) + money)
##        self.setValue(self.COL_UNPAID, self.getValue(self.COL_UNPAID) - money)
##        self.update()


    @classmethod
    def getListUnaudit(cls):
        sql = u'''SELECT saleorder.id, customer.name, saleorder.total, sum(fund_customer.money),
                       saleorder.discount, saleorder.total-sum(fund_customer.money)-saleorder.discount,       
                       saleorder.billdate, saleorder.state,delivery.name,saleorder.memo,saleorder.deliveryPlan,       
                       if(saleorder.printed,'√','×'),if (mod(saleorder.audit,2)=0,'未审核','已审核'),staff_1.name,staff_2.name       
                from saleorder left join customer on saleorder.peerId=customer.id
                                left join fund_customer on saleorder.id=fund_customer.billid 
                                left join delivery on saleorder.dlvyid=delivery.id 
                                left join staff as staff_1 on saleorder.handlerId=staff_1.id  
                                left join staff as staff_2 on saleorder.recorderId=staff_2.id
                where mod(saleorder.audit,2)=0
                group by saleorder.id 
                order by saleorder.id desc
                '''
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByDay(cls, dateStart, dateEnd):
        sql = '''select billDate,sum(total),sum(discount), sum(total)-sum(discount)
                from %s
                where billDate between '%s' and '%s'
                group by billDate order by billDate desc
                ''' %(cls.tbl, dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByMonth(cls, year=0):
        
        filter = "where date_format(billDate, '%%Y')=%s"%year if year else ""
        sql = '''select date_format(billDate, '%%Y-%%m') as month,sum(total),sum(discount) , sum(total)-sum(discount)
                from %s 
                %s
                group by month order by month
                ''' %(cls.tbl, filter)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByCustomer(cls, dateStart, dateEnd, showAll=False):
        
        # 这种统计连同没有销售的数据一起统计了，初衷是可以看到所有客户
        # 并且可以分辨出那些客户最近没有销售
        # 后来考虑，似乎没什么用处
        if showAll:
            sql = '''select name, sum(total), sum(discount), sum(money)
                from (select name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount) as money
                        from customer left join saleOrder 
                                    on customer.id=saleOrder.peerId    
                        where (billDate between '%s' and '%s')       
                        group by name
                    union
                        select customer.name, 0.0 as total, 0.0 as discount, 0.0
                            from customer ) t                     
                group by t.name'''%(dateStart, dateEnd)
        else:
            sql = '''select name,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount) as money
                        from customer left join saleOrder 
                                    on customer.id=saleOrder.peerId    
                        where (billDate between '%s' and '%s')       
                    group by name'''%(dateStart, dateEnd)
                    
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByCustomerType(cls, dateStart, dateEnd):
        
        sql = '''select customer_type.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount)
                from saleOrder,customer,customer_type
                where billDate between '%s' and '%s'
                      and saleOrder.peerId=customer.id
                      and customer.type=customer_type.id
                group by customer_type.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByCustomerRegion(cls, dateStart, dateEnd):
        
        sql = '''select customer_region.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount)
                from saleOrder,customer,customer_region
                where billDate between '%s' and '%s'
                      and saleOrder.peerId=customer.id
                      and customer.region=customer_region.id
                group by customer_region.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByStaff(cls, dateStart, dateEnd):
        
        sql = '''select staff.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount)
                from saleOrder,staff
                where billDate between '%s' and '%s'
                      and saleOrder.handlerId=staff.id
                group by staff.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByDelivery(cls, dateStart, dateEnd):
        
        sql = '''select 'delivery',sum(total) as total,sum(discount) as discount, sum(total)-sum(discount)
                from saleOrder
                where billDate between '%s' and '%s'
                      and deliveryPlan is not Null
                union
                select 'other',sum(total) as total,sum(discount) as discount, sum(total)-sum(discount)
                from saleOrder
                where billDate between '%s' and '%s'
                      and deliveryPlan is Null  '''%(dateStart, dateEnd,dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByProduct(cls, dateStart, dateEnd, query=''):
        '''查询产品销售统计
        
            最大、最小采购价格会大幅延长查询时间，不过如果query存在还是会大幅缩短的
        '''
        filter = u''
        if query:
            filter = u'''and (product.name like '%%{0}%%'
                            or product.spec like '%%{0}%%'
                            or product.briefCode like '%%{0}%%')'''.format(query)
            
        sql = u'''select product.name,product.spec,sum(saledetails.num), sum(saledetails.total),
                        min(saledetails.price),max(saledetails.price)
                from product,saledetails,saleorder
                where saledetails.proId=product.id
                      and saleorder.id=saledetails.billid
                      and saleorder.billDate between '%s' and '%s'
                      %s
                group by product.name'''%(dateStart, dateEnd, filter)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def valid_total(cls):
        
        sql = '''select customer.name,
                        saleorder.id, 
                        saleorder.total, 
                        sum(saledetails.total) as total2, 
                        (saleorder.total-sum(saledetails.total)) as diff
                from saleorder,saledetails,customer
                where saleorder.id = saledetails.billId    
                    and  saleorder.peerId=  customer.id        
                group by saleorder.id'''
        
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def valid_paid():
        
        sql = '''select customer.name,
                        saleorder.id, 
                        saleorder.total, 
                        saleorder.discount, 
                        sum(fund_customer.money) as money2, 
                        (saleorder.total-saleorder.discount-sum(fund_customer.money)) as diff
                from saleorder,fund_customer,customer
                where saleorder.id = fund_customer.billId    
                    and  saleorder.peerId=customer.id        
                group by saleorder.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getBillByDate(self, date):
##        sql = u'''SELECT saleorder.id,
##                        a.name as saler,                        
##                        case when saleorder.delete_time is not null then "删除"
##                             when saleorder.update_time is not null then "修改"
##                             else "创建"
##                        end as optype,
##                        b.name as creater,
##                        saleorder.create_time, 
##                        saleorder.update_cnt,                       
##                        c.name as updater,                        
##                        saleorder.update_time,                        
##                        d.name as deleter,
##                        saleorder.delete_time,                        
##                        saleorder.delete_reason
##                from saleorder left join staff a on saleorder.handlerId=a.id
##                               left join staff b on saleorder.recorderId=b.id
##                               left join staff c on saleorder.updater=c.id                               
##                               left join staff d on saleorder.deleter=d.id
##                where saleorder.create_time BETWEEN "%s 00:00:00" AND "%s 23:59:59"
##                    or saleorder.update_time BETWEEN "%s 00:00:00" AND "%s 23:59:59"
##                    or saleorder.delete_time BETWEEN "%s 00:00:00" AND "%s 23:59:59"
##                '''%(date,date,date,date,date,date)
       
        sql = u'''select saleorder.billdate, saleorder.id, "创建", a.name as saler, 
                       b.name as creater, saleorder.create_time, ''       
                from saleorder left join staff a on saleorder.handlerId=a.id
                               left join staff b on saleorder.recorderId=b.id                               
                where saleorder.create_time BETWEEN "{0} 00:00:00" AND "{0} 23:59:59"
                union
                select saleorder.billdate, saleorder.id, "修改", a.name as saler, 
                       b.name as updater, saleorder.update_time, saleorder.update_cnt      
                from saleorder left join staff a on saleorder.handlerId=a.id
                               left join staff b on saleorder.updater=b.id                               
                where saleorder.update_time BETWEEN "{0} 00:00:00" AND "{0} 23:59:59"
                '''.format(date)
##                union
##                select saleorder.billdate, saleorder.id, "删除", a.name as saler, 
##                       b.name as deleter, saleorder.delete_time, saleorder.delete_reason      
##                from saleorder left join staff a on saleorder.handlerId=a.id
##                               left join staff b on saleorder.deleter=b.id                               
##                where saleorder.delete_time BETWEEN "{0} 00:00:00" AND "{0} 23:59:59"
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret 
    
class CDataSaleOrderTemp(CDataSaleOrder):
    tbl = 'saleorder_temp'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    prefix = 'XS'
    suffix = '00000'
    maxBillId = '' #子类类属性要显示声明，否则与父类同
    clsDetail = CDataSaleDetailTemp
    autoId = False
    
    @classmethod
    def audit(cls, billId):
        '''进行审核，需要修改账务、库存（by detail）'''
        print ('audit:', billId)
        #obj = cls.getFirstInstance({cls.COL_ID:billId})
        #super(CDataSaleOrder, obj).insert()
        sql = "insert into saleorder select * from saleorder_temp where id='%s'" %billId
        cnt = dbmngr.dbmngr.execute(sql)
        sql = "insert into saledetails select * from saledetails_temp where billId='%s'" %billId
        cnt = dbmngr.dbmngr.execute(sql)
        cls.deleteBill(billId)
        
        # 账务、库存
        saleObj = CDataSaleOrder.getFirstInstance({CDataSaleOrder.COL_ID:billId})
        saleObj.decrInventory()
        #saleObj.add        
    
    def deleteRefItem(self):
        #CDataSaleDetailTemp.delData({CDataSaleDetailTemp.COL_BILLID:self.getValue(self.COL_ID)})
        CDataSaleDetailTemp.quickDelete({CDataSaleDetailTemp.COL_BILLID:self.getValue(self.COL_ID)})
        
    @classmethod
    def deleteBill(cls, billId):
        '''删除订单及订单明细'''
        CDataSaleDetailTemp.quickDelete({CDataSaleDetailTemp.COL_BILLID:billId})
        cls.quickDelete({cls.COL_ID:billId}) 
            
class CDataDelivery(CDataModel):
    tbl = 'delivery'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'name', 'memo']
    m_fieldCNNames = ['id', 'name', 'memo']
    m_fieldDefVals = [-1,'','']
    [COL_ID, COL_NAME, COL_MEMO] = [i for i in range(3)]
        
class CDataSupplier(CDataModel):
    tbl = u'supplier'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','name','briefCode','addr','email',
                    'phone','fax','linkman','lm_phone',
                    'lm_memo','debt','memo']

    listenerList = []
    m_fieldDefVals = [-1, '','','','','','','','','','', '']
    [COL_ID,COL_NAME,COL_BREIFCODE,COL_ADDR, COL_EMAIL,COL_PHONE,COL_FAX,
    COL_LINKMAN,COL_LMPHONE,COL_LMMEMO,COL_DEBT,COL_MEMO] = [i for i in range(12)]
    
    m_id = 'id'
    m_name = 'name'
    m_bCode = 'briefCode'
    m_addr = 'addr'
    m_email = 'email'
    m_phone = 'phone'
    m_fax = 'fax'
    m_linkman = 'linkman'
    m_lmPhone = 'lm_phone'
    m_lmMemo = 'lm_memo'
    m_InitDebt = 'debt'
    m_memo = 'memo'
    
    def __init__(self):    
        CDataModel.__init__(self)
        
        self.m_fieldVals = [-1, '','','','','','','','','','', '']
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldDefVals = []
        cls.COL_ID = cls.appendField('id', defVal=-1)
        cls.COL_NAME = cls.appendField('name')
        cls.COL_BREIFCODE = cls.appendField('briefCode')
        cls.COL_ADDR = cls.appendField('addr')
        cls.COL_EMAIL = cls.appendField('email')
        cls.COL_PHONE = cls.appendField('phone')
        cls.COL_FAX = cls.appendField('fax')
        cls.COL_LINKMAN = cls.appendField('linkman')
        cls.COL_LMPHONE = cls.appendField('lm_phone')
        cls.COL_LMMEMO = cls.appendField('lm_memo')
        cls.COL_DEBT = cls.appendField('debt')
        cls.COL_MEMO = cls.appendField('memo')
        
        cls.refreshDataList()
        
        cls.listen()
    
    @classmethod    
    def listen(cls):
        CRelFundSupplier.attach(cls.updateExtraByFund)
        CDataPurOrder.attach(cls.updateExtraByOrder)
    
    @classmethod
    def updateExtraByFund(cls, op, fund):
        id = fund.getPeerId()
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        obj.notify()
    
    @classmethod    
    def updateExtraByOrder(cls, op, order):
        id = order.getValue(order.COL_PEERID)
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        obj.notify()
                
    def getDebt(self):
        if self.overDue:
            self.populateExtra()
        return self.debt
        
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
    
    def populateExtra(self):
        #获取欠款情况：应收货款总金额-已收货款总金额+期初欠款
        # 该数据的获取方式决定listen对象
        self.debt = self.getPurTotal() - self.getPaidTotal() + self.getInitDebt()
        self.overDue = False
    
    def getInitDebt(self):
        return self.m_fieldVals[self.COL_DEBT]
    
    def getPurTotal(self):
        peerId = self.getValue(self.COL_ID)
        return CDataPurOrder.getPurTotal(peerId)
    
    def getPaidTotal(self):
        peerId = self.getValue(self.COL_ID)
        return CDataVoucherSupplier.getPaid(peerId)
    
    @classmethod        
    def getProRecent(cls, supId):
        return CDataPurDetail.getProRecent(supId)
                
class CDataPurOrder(CDataModel):
    tbl = 'purOrder'
    m_dataList = []
    m_fieldNo = {}
    #m_fieldNames = ['id', 'peerId', 'billDate', 'warehouseId','total', 'paid',#'unpaid', 
    #                'discount', 'handlerId', 'recorderId', 'memo']
    m_fieldCNNames = []
    m_fieldDefVals = []
    #[COL_ID,COL_PEERID,COL_BILLDATE,COL_WHID,COL_TOTAL,COL_PAID,#COL_UNPAID,
    #COL_DISCOUNT,COL_HANDLERID,COL_RECOREDERID,COL_MEMO] = [i for i in range(10)]
    
    listenerList = []
    autoId = False
    
    prefix = 'CJ'
    suffix = '00000'
    billIdCnt = 0 # 避免同时建立多个新订单时id重复
    
#    def __init__(self):
#        CDataModel.__init__(self)
#        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.COL_ID = cls.appendField('id')
        cls.COL_PEERID = cls.appendField('peerId')
        cls.COL_BILLDATE = cls.appendField('billDate')
        cls.COL_WHID = cls.appendField('warehouseId')
        cls.COL_TOTAL = cls.appendField('total')
        #cls.COL_PAID = cls.appendField('paid')
        cls.COL_DISCOUNT = cls.appendField('discount')
        cls.COL_HANDLERID = cls.appendField('handlerId')
        cls.COL_RECOREDERID = cls.appendField('recorderId')
        cls.COL_MEMO = cls.appendField('memo')
        
        cls.refreshDataList()
                
        #cls.populateDataList()
        
        CRelFundSupplier.attach(cls.updatePaid)
    
    @classmethod
    def updatePaid(cls, op, fund):
        # 还会notify CDataSaleOrderTemp，不过没什么影响，暂时忽略
        orderId = fund.getValue(fund.COL_BILLID)
        print ('notify ', op, orderId)
        obj = cls.findObj(orderId)
        if obj:
            obj.overDue = True
            obj.notify()
    
    @classmethod
    def populateDataList(cls):
        # 除数据库字段外，初始化时需要载入的数据（需要合成，以便UI可以快速反应）
        #index = len(cls.m_fieldNames)
        #cls.COL_PAID = index
        #cls.COL_UNPAID = index + 1
        
        for obj in cls.getObjList():
            obj.populateExtra()
    
    def populateExtra(self):
        self.paid = CRelFundSupplier.getPaidAmount(self.m_fieldVals[self.COL_ID])
        self.overDue = False
        
    def getPaid(self):
        if self.overDue:
            self.populateExtra()
        return self.paid
    
    def getUnpaid(self):
        total = self.m_fieldVals[self.COL_TOTAL]
        discount = self.m_fieldVals[self.COL_DISCOUNT]
        return total - discount - self.getPaid()
        
    @classmethod        
    def deleteRefItem(cls, obj): 
        # CRelFundCustomer
        id = obj.getValue(obj.COL_ID)
        CRelFundSupplier.quickDelete({CRelFundSupplier.COL_BILLID:id})
        
        # CDataSaleDetail
        CDataPurDetail.quickDelete({CDataPurDetail.COL_BILLID:id})

    @classmethod
    def clearDetail(cls, id):
        CDataPurDetail.quickDelete({CDataPurDetail.COL_BILLID:id})
            
    @classmethod
    def deleteBill(cls, billId, delFund=True):
        '''删除订单及订单明细'''
        # 添加收款单后，还需删除相关收款记录
        # 编辑订单时，如果id没有变化，则不需要解除收款关系
        # 150420 - 实际上，编辑订单时应该用update，现在用的是del/insert才这么多麻烦
        if delFund:
            CRelFundSupplier.quickDelete({CRelFundSupplier.COL_BILLID:billId})

        CDataPurDetail.quickDelete({CDataPurDetail.COL_BILLID:billId})
        cls.quickDelete({cls.COL_ID:billId})
    
    @classmethod
    def getBillAmount(cls, peerId):
        '''
            return - [billId,amount,paid,discount,unpaid]
        '''
    
##        sql = 'select id,total,discount '
##        sql += 'from %s where peerId=%d order by id desc' %(cls.tbl, peerId)
##                
##        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt > 0):
##            return dbmngr.dbmngr.cursor.fetchall()
##        else:
##            return []
        
        #list = cls.getObjs({cls.COL_PEERID:peerId})
        list = cls.fetch('peerId=%d'%peerId)
        data = []
        for obj in list:
            data.append([obj.getValue(obj.COL_ID),
                        obj.getValue(obj.COL_TOTAL),
                        obj.getPaid(),
                        obj.getValue(obj.COL_DISCOUNT),
                        obj.getUnpaid()])
        return data
        
    @classmethod
    def getProDetail(cls, billId):
        '''return - [[id,name,unit,cnt,price,total],[...]] 与界面显示保持一致'''
        return CDataPurDetail.getProDetail(billId)     
    
    @classmethod
    def getPurTotal(cls, peerId):
        '''获取应收总额（扣减优惠额之后的）'''
        sql = 'select sum(total),sum(discount) from %s where peerId=%s' %(cls.tbl,peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None,None,None,None)will be returned if the custid unfound
        ret =  dbmngr.dbmngr.cursor.fetchone()
        if(ret[0] is None):
            ret = (0.0, 0.0)
        return ret[0] - ret[1]

    @classmethod
    def valid_total(cls):
        
        sql = '''select supplier.name,
                        purorder.id, 
                        purorder.total, 
                        sum(purdetails.total) as total2, 
                        (purorder.total-sum(purdetails.total)) as diff
                from purorder,purdetails,supplier
                where purorder.id = purdetails.billId    
                    and  purorder.peerId=  supplier.id        
                group by purorder.id'''
        
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def valid_paid():
        
        sql = '''select supplier.name,
                        purorder.id, 
                        purorder.total,
                        purorder.discount,
                        sum(fund_supplier.money) as money2, 
                        (purorder.total-purorder.discount-sum(fund_supplier.money)) as diff
                from purorder,fund_supplier,supplier
                where purorder.id = fund_supplier.billId    
                    and  purorder.peerId=  supplier.id        
                group by purorder.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
class CDataPurDetail(CDataModel):
    tbl = 'purDetails'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['detail_id','billId', 'peerId','proId', 'proName','proSpec', 'unit','price', 'num', 'total', 'memo']
    #m_fieldCNNames = ['detail_id','saleId', 'custId', 'proId','proName','proSpec','unit', 'price', 'num', 'total']
    m_fieldDefVals = [''] * 11
    [COL_DETAILID,COL_BILLID,COL_PEERID,COL_PROID,COL_PRONAME,COL_PROSPEC,COL_UNIT,COL_PRICE,
    COL_NUM,COL_TOTAL, COL_MEMO] = [i for i in range(11)]
    fi_key = 0
                    
##    def __init__(self):
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    def incrInventory(self, incr=True):
        proId = self.getValue(self.COL_PROID)
        unit = self.getValue(self.COL_UNIT)
        num = self.getValue(self.COL_NUM)
        
        if not(incr):
            num = -num
        CDataProduct.updateOnhand(proId, unit, num)

    def insert(self):
        # update pro info
        proId = self.getValue(self.COL_PROID)
        unit = self.getValue(self.COL_UNIT)
        price = self.getValue(self.COL_PRICE)
        num = self.getValue(self.COL_NUM)
        CDataProduct.updatePurchase(int(proId), unit, float(price), float(num))
        
        # insert self
        return super(CDataPurDetail, self).insert_with_autoid()
    
    def delete(self):
        self.incrInventory(False)
        return super(CDataPurDetail, self).delete()
    
    @classmethod
    def adjustInventory(cls, fields, incr=True):
        sql = 'select proId,unit,num from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
        
        cnt = dbmngr.dbmngr.execute(sql)
        result = dbmngr.dbmngr.cursor.fetchall()
        for proId,unit,num in result:
            if not(incr):
                num = -num
            CDataProduct.updateOnhand(proId, unit, num)
            
    @classmethod
    def delData(cls, fields=None):
        cls.adjustInventory(fields, False)
        sql = 'delete from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
            
        return dbmngr.dbmngr.execute(sql)
                
    @classmethod
    def getProDetail(cls, billId):
        sql = "select proId,proName,proSpec,unit,num,price,total,memo "\
                "from %s "\
                "where billId='%s'" %(cls.tbl, billId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getProRecent(cls, peerId):
        data = []
        stat = cls.getStat(peerId)
        for proId,cnt,amount in stat:
            #如果有统计，则必有消费,[0]为最近订单信息
            proName,unit,price,billId,num,total,memo = cls.getProSaleInfo(proId,peerId)[0]
            data.append([billId,proId,proName,unit,price,cnt,amount])
        
        return data    
        
    @classmethod
    def getStat(cls, peerId):
        sql = "select proId,sum(num),sum(total) "\
                "from %s " \
                "where peerId=%s group by proId" %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getProSaleInfo(cls, proId, peerId=-1):
        if(peerId != -1):
            resultStr = 'proName,unit,price,billId,num,total,memo'
            filterStr = 'proId=%s and peerId=%s' %(proId, peerId)
        else:
            resultStr = 'proName,unit,price,billId,num,total,peerId,memo'
            filterStr = 'proId=%s' %proId
            
        sql = "select %s "\
                "from %s " \
                "where %s order by billId desc" %(resultStr, cls.tbl, filterStr)    
         
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

class CDataPeer(CDataModel):
    tbl = 'peer'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')
        cls.fi_briefCode = cls.appendField('briefCode')
        cls.fi_addr = cls.appendField('addr')
        cls.fi_email = cls.appendField('email')
        cls.fi_phone = cls.appendField('phone')
        cls.fi_fax = cls.appendField('fax')
        cls.fi_memo = cls.appendField('memo')
        cls.fi_linkman = cls.appendField('linkman')
        cls.fi_lmPhone = cls.appendField('lm_phone')
        cls.fi_lmMemo = cls.appendField('lm_memo')
        cls.fi_debt = cls.appendField('debt')
        
        cls.refreshDataList()
        
    def getDebt(self):
        id = self.getKeyVal()
        return CDataPeerFund.getAmount(id)+CDataPeerLend.getAmount(id)
        
class CDataPeerFund(CDataModel):
    '''支出为正，收入为负
    '''
    tbl = 'peer_fund'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_peerId = cls.appendField('peerId')
        cls.fi_account = cls.appendField('account')
        cls.fi_money = cls.appendField('money')
        cls.fi_date = cls.appendField('date')
        cls.fi_handler = cls.appendField('handler')
        cls.fi_recorder = cls.appendField('recorder')
        cls.fi_memo = cls.appendField('memo')
        
        cls.refreshDataList()
    
    @classmethod
    def getAmount(cls, peerId):
        sql = 'select sum(money) from %s where peerId=%d' %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            return result[0]
        else:
            return 0.0
    
    def getAccountName(self):
        obj = CDataAccount.findObj(self.getValue(self.fi_account))
        return obj.getValue(obj.COL_BRIEFNAME)
                                
class CDataPeerLend(CDataModel):
    '''把同行当做客户对待，借出为正，借入为负
    '''
    tbl = 'peer_lend'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_peerId = cls.appendField('peerId')
        cls.fi_date = cls.appendField('date')
        cls.fi_proId = cls.appendField('proId')
        cls.fi_proName = cls.appendField('proName')
        cls.fi_proSpec = cls.appendField('proSpec')
        cls.fi_unit = cls.appendField('unit')
        cls.fi_num = cls.appendField('num')
        cls.fi_price = cls.appendField('price')
        cls.fi_total = cls.appendField('total')
        cls.fi_handler = cls.appendField('handler')
        cls.fi_recorder = cls.appendField('recorder')
        cls.fi_memo = cls.appendField('memo')
        
        cls.refreshDataList()
    
    @classmethod
    def getAmount(cls, peerId):
        sql = 'select sum(total) from %s where peerId=%d' %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            return result[0]
        else:
            return 0.0

        
                                    
class CDataBackup(CDataModel):
    tbl = 'backup'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'backupQuit', 'path', 'code','autoEmail','email','psw','lastFile']
    m_fieldDefVals = ['']*8
    
    [COL_ID,COL_BACKUPQUIT,COL_PATH,COL_CODE,COL_AUTOEMAIL,COL_EMAIL,
    COL_PSW,COL_LASTFILE] = [i for i in range(8)]

    @classmethod
    def getBackupConfig(cls):
        return cls.m_dataList[0]

    @classmethod
    def isBackupQuit(cls):
        return cls.m_dataList[0].m_fieldVals[cls.COL_BACKUPQUIT]
     
    @classmethod
    def updateLastFile(cls, file):
        file = file.replace('\\', '/')
        cls.m_dataList[0].m_fieldVals[cls.COL_LASTFILE] = file
        cls.m_dataList[0].update()

class CDataVersion(CDataModel):
    tbl = 'version'
    m_dataList = []
    m_fieldNo = {}
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'version']
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iVersion = cls.appendField('version')
        cls.iInfo = cls.appendField('info')
        
        cls.refreshDataList()
    
    @classmethod
    def getVersion(cls):
        if not(cls.m_dataList):
            return '0.0'
        
        return cls.m_dataList[0].m_fieldVals[cls.iVersion]
        
class CDataVoucher(CDataModel):
    tbl = 'voucher'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldDefVals = []
    listenerList = []
    
##    m_fieldNames = ['id', 'peerId', 'accountId', 'money','date','handlerId',
##                    'recorderId','memo']
    autoId = False
    prefix = 'KF'
    suffix = '0000'
    [COL_ID,COL_PEERID,COL_ACCOUNTID,COL_MONEY,COL_DATE,COL_HANDLERID,
    COL_RECORDERID,COL_MEMO] = [i for i in range(8)]

    # def __init__(self):
        # CDataModel.__init__(self)
        # self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iPeerId = cls.appendField('peerId')
        cls.iAccountId = cls.appendField('accountId')
        cls.iMoney = cls.appendField('money')
        cls.iDate = cls.appendField('date')
        cls.iHandlerId = cls.appendField('handlerId')
        cls.iRecorderId = cls.appendField('recorderId')
        cls.iMemo = cls.appendField('memo')
        
        cls.refreshDataList()
    
    def getAccountDetail(self):
        return CDataAccountDetail.getObjs({CDataAccountDetail.COL_FUNDID:self.getKeyVal()})

    
    def getSaleId(self):
        ret = CRelFundCustomer.getBillId(self.getValue(CDataVoucher.COL_ID))
        if not(ret):
            return ret[0]
        else:
            return ''
    
    def getBillId(self):
        '''获取关联的订单id列表'''
        return CRelFundCustomer.getBillId(self.getValue(self.COL_ID))
    
    def getAllocateInfo(self):
        return CRelFundCustomer.getAllocateInfo(self.getValue(self.COL_ID))
        
    @classmethod
    def getCustPaid(cls, custId):
        ret = 0
        sql = 'select sum(money) from %s where peerId=%s' %(cls.tbl,custId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            ret = result[0]
        return ret
    
    @classmethod
    def deleteRefItem(cls, obj): 
        id = obj.getValue(obj.COL_ID)
        CRelFundCustomer.quickDelete({CRelFundCustomer.COL_VOUCHERID:id})
        
        # 不能简单删除资金明细，只要删除关系
        #CDataAccountDetail.quickDelete({CDataAccountDetail.COL_FUNDID:id})
        CDataAccountDetail.detachVoucher(id)
        
    @classmethod
    def deepDelData(cls, voucherId):
        '''深度删除，把相关表数据一起删除'''
        cls.deepDeleteById(voucherId)
##        CRelFundCustomer.quickDelete({CRelFundCustomer.COL_VOUCHERID:voucherId})
##        CDataAccountDetail.quickDelete({CDataAccountDetail.COL_FUNDID:voucherId})
##        cls.quickDelete({cls.COL_ID:voucherId})
    
    def getAccountName(self):
        accountObj = CDataAccount.findObj(self.getValue(self.iAccountId))
        return accountObj.getValue(accountObj.COL_BRIEFNAME)
    
    @classmethod
    def getPeerName(cls, voucherId):
        voucherObj = cls.findObj(voucherId)
        peerObj = CDataCustomer.findObj(voucherObj.getValue(voucherObj.iPeerId))
        return peerObj.getValue(peerObj.COL_NAME)
    
    @staticmethod
    def valid_alloct():
        sql = '''select customer.name,
                        voucher.id, 
                        voucher.money, 
                        sum(fund_customer.money) as money2, 
                        (voucher.money-sum(fund_customer.money)) as diff
                from voucher,fund_customer,customer
                where voucher.id = fund_customer.voucherId    
                    and  voucher.peerId=  customer.id        
                group by voucher.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
                
class CDataVoucherSupplier(CDataModel):
    tbl = 'voucher_supplier'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldDefVals = []
    autoId = False
    prefix = 'GF'
    suffix = '0000'
    listenerList = []

    # 为了和CDataVoucher保持一致
    [COL_ID,COL_PEERID,COL_ACCOUNTID,COL_MONEY,COL_DATE,COL_HANDLERID,
    COL_RECORDERID,COL_MEMO] = [i for i in range(8)]
    
    # def __init__(self):
        # CDataModel.__init__(self)
        # self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iPeerId = cls.appendField('peerId')
        cls.iAccountId = cls.appendField('accountId')
        cls.iMoney = cls.appendField('money')
        cls.iDate = cls.appendField('date')
        cls.iHandlerId = cls.appendField('handlerId')
        cls.iRecorderId = cls.appendField('recorderId')
        cls.iMemo = cls.appendField('memo')
        
        cls.refreshDataList()
    
    def getAccountDetail(self):
        return CDataAccountDetail.getObjs({CDataAccountDetail.COL_FUNDID:self.getKeyVal()})
        
    def getAccountName(self):
        accountObj = CDataAccount.findObj(self.getValue(self.iAccountId))
        return accountObj.getValue(accountObj.COL_BRIEFNAME)
    
    @classmethod
    def getPeerName(cls, voucherId):
        voucherObj = cls.findObj(voucherId)
        peerObj = CDataSupplier.findObj(voucherObj.getValue(voucherObj.iPeerId))
        return peerObj.getValue(peerObj.COL_NAME)
    
    @classmethod
    def getPaid(cls, peerId):
        ret = 0
        sql = 'select sum(money) from %s where peerId=%s' %(cls.tbl,peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            ret = result[0]
        return ret
    
    @classmethod
    def deleteRefItem(cls, obj):
        id = obj.getValue(obj.COL_ID)
        CRelFundSupplier.delData({CRelFundSupplier.COL_VOUCHERID:id})
        CDataAccountDetail.delData({CDataAccountDetail.COL_FUNDID:id})
    
    @classmethod
    def deepDelData(cls, voucherId):
        '''深度删除，把相关表数据一起删除'''
        cls.deepDeleteById(voucherId)
        
    def getBillId(self):
        '''获取关联的订单id列表'''
        return CRelFundSupplier.getBillId(self.getValue(self.COL_ID))
    
    def getAllocateInfo(self):
        return CRelFundSupplier.getAllocateInfo(self.getValue(self.COL_ID))

    @staticmethod
    def valid_alloct():
        sql = '''select supplier.name,
                        voucher_supplier.id, 
                        voucher_supplier.money, 
                        sum(fund_supplier.money) as money2, 
                        (voucher_supplier.money-sum(fund_supplier.money)) as diff
                from voucher_supplier,fund_supplier, supplier
                where voucher_supplier.id = fund_supplier.voucherId    
                    and  voucher_supplier.peerId=  supplier.id        
                group by voucher_supplier.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
class CDataAccountSubject(CDataModel):
    tbl = 'account_subject'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'科目名称')
        cls.fi_income = cls.appendField('income', u'收支类型')
        
        cls.refreshDataList()
    
    def getValue(self, index):
        if index == self.fi_income:
            return u'收入' if self.m_fieldVals[index] else u'支出'
        
        return self.m_fieldVals[index]
    
    @classmethod
    def getDefName(cls, income=True):
        return u'主营业务收入' if income else u'主营业务支出'
            
    @classmethod
    def fetchSubjectList(cls, income):
        sql = 'select id,name from %s where income=%d' %(cls.tbl, income)
        cnt = dbmngr.dbmngr.execute(sql)        
        return dbmngr.dbmngr.cursor.fetchall()
                        
class CDataAccount(CDataModel):
    tbl = 'account'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'briefName', 'fullName','bIncome', 'bDefray','memo', 'openingBalance']
    m_fieldCNNames = [u'编号', u'账户简名',  u'账户全名', u'', u'', u'备注', u'']
    m_fieldDefVals = [-1, '', '', 'T', 'T', '', 0]
    [COL_ID,COL_BRIEFNAME,COL_FULLNAME,COL_BINCOME,COL_BDEFRAY,
    COL_MEMO, COL_INITAMOUNT] = [i for i in range(7)]
    
    def __init__(self):    
        CDataModel.__init__(self)
        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
        #除非在数据模型中存在的全部是string类型
        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
        #        所以为之新增函数
        #id = self.newAutoIncrId()
        self.m_fieldVals = [-1, '', '', 'T', 'T', '', 0]
    
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
        
    @staticmethod
    def getDefAccount():
        return u'现金'

    @classmethod
    def getAccountNames(cls):
        sql = 'select id,briefName from '
        
class CDataAccountDetail(CDataModel):
    tbl = 'accountDetails'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','accountId', 'date', 'money', 'fundId','memo','sysmemo',
                    'time', 'subject','loadIn','useage']
    #m_fieldCNNames = [u'编号', u'账户简名',  u'账户全名', u'', u'', u'备注']
    m_fieldDefVals = [-1, '', '', '', None, '','',None,None,0,'']
    [COL_ID,COL_ACCOUNTID,COL_DATE,COL_MONEY,COL_FUNDID,
    COL_MEMO,COL_SYSMEMO, COL_TIME, COL_SUBJECT,COL_LOADIN,COL_USEAGE] = [i for i in range(11)]
    
##    def __init__(self):    
##        CDataModel.__init__(self)
##        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
##        #除非在数据模型中存在的全部是string类型
##        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
##        #        所以为之新增函数
##        #id = self.newAutoIncrId()
##        self.m_fieldVals = [-1, '', '', '', '', '','','','']
     
##    def assignNewId(self):
##        id = self.newAutoIncrId()
##        self.setValue(self.COL_ID, id)
##        return id
    
##    @classmethod
##    def createNew(cls):
##        obj = cls()
##        obj.setValue(cls.COL_ID, cls.newAutoIncrId())
##        return obj
    
    @classmethod
    def getMaxDate(cls, accountId):
        sql = 'select max(date) from %s where accountId=%d' %(cls.tbl, accountId)
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchone()[0]
    
    def getAccount(self):
        return CDataAccount.getFirstInstance({CDataAccount.COL_ID:self.m_fieldVals[self.COL_ACCOUNTID]})
    
    def getSubjectName(self):
        subjectId = self.getValue(self.COL_SUBJECT)
        if subjectId is None or subjectId == '':
            return ''
            
        subjectObj = CDataAccountSubject.findObj(subjectId)
        return subjectObj.getValue(subjectObj.fi_name)
    
    @classmethod
    def detachVoucher(cls, voucherId):
        objList = cls.getObjs({cls.COL_FUNDID:voucherId})
        if (len(objList) > 1):
            msg = u'Error:voucherId:%s match more than one accountdetail%s' %(voucherId, objList)
            kit.showModal(msg)
            
        for obj in objList:
            obj.setValue(obj.COL_FUNDID, None)
            obj.update()
    
    
    
class CDataCompany(CDataModel):
    tbl = 'company'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'name', 'briefName', 'phone', 'addr', 'business']
    m_fieldDefVals = ['']*6
    [COL_ID, COL_NAME, COL_BRIEFNAME,COL_PHONE,COL_ADDR,COL_BUSINESS] = [i for i in range(6)]
    
    @classmethod
    def getCompanyInfo(cls):
        return cls.m_dataList[0]
    
class CDataStaff(CDataModel):
    tbl = 'staff'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','name','post','phone','addr','memo',
                    'emergency', 'isOperator','user','psw', 'limited', 'group_id', 'active']
    m_fieldCNNames = [u'id', u'员工姓名',  u'职务', u'联系电话', u'联系地址', 
                    u'备注', u'紧急联系', u'是否操作员', u'', u'', u'', u'启用']
    m_fieldDefVals = [-1, '', '', '', '', '', '', 'F', '', '', 1, None, 0]
    [COL_ID,COL_NAME,COL_POST,COL_PHONE,COL_ADDR,COL_MEMO,COL_EMERGENCY,
    COL_ISOPERATOR, COL_USER, COL_PSW, COL_LIMITED, COL_GROUPID, COL_ACTIVE] = [i for i in range(13)]

    def __init__(self):    
        CDataModel.__init__(self)
        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
        #除非在数据模型中存在的全部是string类型
        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
        #        所以为之新增函数
        #id = self.newAutoIncrId()
        self.m_fieldVals = [-1, '', '', '', '', '', '', 'F', '', '', 1, None,0]
    
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
        
    @staticmethod
    def isOperator(staffObj):
        return staffObj.getFieldVal(index=CDataStaff.COL_ISOPERATOR)==1
    
    def isLimited(self):
        return True if self.getValue(self.COL_LIMITED) else False
    
    @classmethod
    def getName(cls, id):
        return cls.findItemValue({cls.COL_ID:id}, cls.COL_NAME)
        
    def getValue(self, index):
        if (index == self.COL_ISOPERATOR):
            if (self.m_fieldVals[index] == 'T'):
                return u'是'
            else:
                return u'否'
            
        return self.m_fieldVals[index]
    
    @classmethod
    def login(cls, user, psw):
        for obj in cls.m_dataList:
            if (obj.m_fieldVals[cls.COL_ISOPERATOR] == 'T'):
                if (obj.m_fieldVals[cls.COL_USER] == user and
                    obj.m_fieldVals[cls.COL_PSW] == psw):
                    return obj
        return None
        
    def isLimited(self):
        return self.m_fieldVals[self.COL_LIMITED]
     
class CDataEvent(CDataModel):
    tbl = 'event'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','title','content','bootAlarm', 'startDate']
    m_fieldDefVals = ['']*5
    [iId, iTitle, iContent, ibootAlarm, iStartDate] = [i for i in range(5)]
    autoId = True
    
    m_fieldCNNames = [u'编号', u'标题',  u'内容', u'是否提醒', u'开始时间']
                    
    def getValue(self, index):
        if (index == self.ibootAlarm):
            if (self.m_fieldVals[index] == 'T'):
                return u'是'
            else:
                return u'否'
            
        return super(CDataEvent, self).getValue(index)  
    
    @classmethod
    def getExpiredEvent(cls, now, last=None):
        sql = "select id,title,content,startDate from %s where startDate<='%s'"%(cls.tbl, now)
        if (last is not None):
            sql += " and startDate>'%s'" %last 
             
        cnt = dbmngr.dbmngr.execute(sql)
        if (0 == cnt):
            return []
        
        return dbmngr.dbmngr.cursor.fetchall()
    
    @classmethod
    def getExpiredNum(cls, now, last=None):
        sql = "select count(*) from %s where startDate<='%s'"%(cls.tbl, now)
        if (last is not None):
            sql += " and startDate>'%s'" %last 
             
        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0]

class CDataRecord(CDataModel):
    tbl = 'record'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_title = cls.appendField('title', u'标题')
        cls.fi_type = cls.appendField('type', u'类型')
        cls.fi_content = cls.appendField('content', u'内容')
        cls.fi_feedback = cls.appendField('feedback', u'反馈')
        
        cls.refreshDataList()

class CDataLog(CDataModel):
    tbl = 'log'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #super(CDataLog, cls).initClsData()
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_date = cls.appendField('date', u'时间')
        cls.fi_level = cls.appendField('level', u'级别')
        cls.fi_log = cls.appendField('log', u'内容')
        
        # 数量多了之后会影响启动效率
        #cls.refreshDataList()

    @classmethod
    def add(cls, lvl, fmt, args):
        if (len(args) == 1
            and (isinstance(args[0], tuple) or isinstance(args[0], list))):
            param = args[0]
        else:
            param = args
               
        #print fmt
        #print param
        log = fmt %param
        cls.quickInsert({cls.fi_date:datetime.datetime.today(),
                        cls.fi_level:lvl,
                        cls.fi_log:log})   
        
    @classmethod
    def debug(cls, fmt, *args):
        cls.add('debug', fmt, args)
        
    @classmethod
    def info(cls, fmt, *args):
        cls.add('info', fmt, args)
        
    @classmethod
    def error(cls, fmt, *args):
        cls.add('error', fmt, args)
        
    @classmethod
    def warning(cls, fmt, *args):
        cls.add('warning', fmt, args)
        
    @classmethod
    def critical(cls, fmt, *args):
        cls.add('critical', fmt, args)
        
    @classmethod
    def exception(cls, fmt, *args):
        cls.add('exception', fmt, args)
        
class CDataTip(CDataModel):
    tbl = 'tip'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_tip = cls.appendField('tip', u'')
        
        cls.refreshDataList()
        
    @classmethod
    def getRandomTip(cls):
        import random
        index = random.randint(1, len(cls.m_dataList) - 1)
        return cls.m_dataList[index].getValue(cls.fi_tip)
                                                  
##class CDataOperator(CDataModel):
##    tbl = 'operator'
##    m_dataList = []
##    m_fieldNo = {}
##    m_fieldNames = ['id','staffId','user','psw']
##    [COL_ID,COL_STAFFID,COL_USER,COL_PSW] = [i for i in range(4)]

class CDataDeliveryRoute(CDataModel):
    tbl = 'delivery_route'
    autoId = True
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')
        cls.fi_memo = cls.appendField('memo')
    
    @classmethod
    def getRegionList(cls, id):
        return CDataDeliveryRouteRegion.getRegionList(id)
    
    @classmethod
    def getRegionListStr(cls, id):
        sql = "select b.name from delivery_route_region a, customer_region b "\
            "where a.routeId=%d and a.regionId=b.id "\
            "order by a.seq" %(id)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt <= 0:
            return []
    
        ret = dbmngr.dbmngr.cursor.fetchall()
        str = u''
        for item in ret:
            str += item[0] + u','
        return str[:-1]
 
    
    @classmethod
    def deleteRefItem(cls, obj):
        '''子类根据需要选择删除关联数据表中的数据'''
        # 删除delivery_route_region
        CDataDeliveryRouteRegion.quickDelete({CDataDeliveryRouteRegion.fi_routeId:obj.getKeyVal()})
            
class CDataDeliveryRouteRegion(CDataModel):
    tbl = 'delivery_route_region'
    fi_key=-1
    
    @classmethod
    def makeField(cls):
        cls.fi_routeId = cls.appendField('routeId')
        cls.fi_regionId = cls.appendField('regionId')
        cls.fi_index = cls.appendField('seq')

    @classmethod
    def getRegionList(cls, routeId):
        sql = "select regionId,seq from %s where routeId=%d order by seq" %(cls.tbl, routeId)
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
        #sql = "select regionId from %s where routeId='%s' order by index asc" %(cls.tbl, routeId)
##        sql = "select b.name from delivery_route_region a, customer_region b "\
##            "where a.routeId='%s' and a.regionId=b.id "\
##            "order by a.index asc" %(routeId)
##        cnt = dbmngr.dbmngr.execute(sql)
##        if cnt <= 0:
##            return []
##    
##        ret = dbmngr.dbmngr.cursor.fetchall()
##        str = u''
##        for item in ret:
##            str += item[0] + u','
##        return str[:-1]

        
class CDataDeliveryPlan(CDataModel):
    tbl = 'delivery_plan'
    
    autoId = False
    prefix = 'ZS'
    suffix = '000'
    maxBillId = ''
    #记录已经申请的ID数量，避免同时实例化多个变量出现的ID重复申请
    #Note: 子类中应该重新声明该变量，以免被其他子类修改
    billIdCnt = 0
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id')
        cls.fi_planDate = cls.appendField('plan_date')
        cls.fi_sendDate = cls.appendField('send_date')
        cls.fi_returnDate = cls.appendField('return_date')
        cls.fi_truck = cls.appendField('truck', defVal=None)
        cls.fi_driver = cls.appendField('driver', defVal=None)
        cls.fi_follower1 = cls.appendField('follower1', defVal=None)
        cls.fi_follower2 = cls.appendField('follower2', defVal=None)
        cls.fi_routeId = cls.appendField('routeId')
        cls.fi_state = cls.appendField('state', defVal=None)
        cls.fi_memo = cls.appendField('memo')

    @classmethod
    def tryDelete(cls, id):
        import wx

        # 查找相关数据
        #orderList = CDataSaleOrder.getObjs({CDataSaleOrder.COL_DELIVERYPLAN:id})
        sql = 'deliveryPlan="%s"'%(id)
        orderList = CDataSaleOrder.fetch(sql)
        if orderList:
            msg = u'有%d个销售单关联到该直送计划单，是否解除关联？' %(len(orderList))
            dlg = wx.MessageDialog(None, msg, u'消息框',wx.YES_NO|wx.ICON_QUESTION)
            if dlg.ShowModal() != wx.ID_YES:
                return False
            
            for obj in orderList:
                #print 
                print obj.m_fieldNames
                print obj.m_fieldVals
                obj.setValue(CDataSaleOrder.COL_DELIVERYPLAN, None)
                obj.update()
                
        cls.delData({cls.fi_id:id})
        return True  
                
        
    def getTruckName(self):
        if self.getValue(self.fi_truck):
            return self.getValue(self.fi_truck)
        else:
            return u''
        
    def getRouteName(self):
        routeId = self.getValue(self.fi_routeId)
        routeObj = CDataDeliveryRoute.findObj(routeId)
        return routeObj.getValue(routeObj.fi_name)
    
    def getStateName(self):
        stateId = self.getValue(self.fi_state)
        if not stateId:
            return u''
        
        stateObj = CDataDeliveryState.findObj(stateId)
        return stateObj.getValue(stateObj.fi_name)
    
    def getDriverName(self):
        driverId = self.getValue(self.fi_driver)
        if not driverId:
            return u''
        
        obj = CDataStaff.findObj(driverId)
        return obj.getValue(obj.COL_NAME)
    
    def getFollowerName(self):
        id = self.getValue(self.fi_follower1)
        if not id:
            return u''
        
        obj = CDataStaff.findObj(id)
        return obj.getValue(obj.COL_NAME)
    
    @classmethod
    def quickAdd(cls, routeId):
        obj = cls.factory(True)
        obj.setValue(obj.fi_planDate, datetime.datetime.today().date())
        obj.setValue(obj.fi_sendDate, datetime.datetime.today().date())
        obj.setValue(obj.fi_returnDate, datetime.datetime.today().date())
        obj.setValue(obj.fi_routeId, routeId)
        obj.setValue(obj.fi_state, CDataDeliveryState.getDefState().getKeyVal())
        obj.insert()
        return obj
        
    @classmethod
    def getNewId(cls):
        '''子类可以覆盖，以定义自己的id产生方式'''
        return CDataDeliveryPlan.newBillId(datetime.datetime.today().strftime('%Y%m%d'))
            
class CDataDeliveryState(CDataModel):
    tbl = 'delivery_state'
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')

    @classmethod
    def getDefState(cls):
        return cls.findObj(1)
    
class CDataTruck(CDataModel):
    tbl = 'truck'
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'车牌号')
        cls.fi_memo = cls.appendField('name')
                    
class CDataTableSale(CDataModel):
    tbl = 'table_sale'
    m_dataList = []
    m_fieldNo = {}
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'title','footer','buyer','debt']
    #[iId, iTitle, iFooter, iBuyer] = [i for i in range(4)]
    autoId = True
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iTitle = cls.appendField('title')
        cls.iFooter = cls.appendField('footer')
        cls.iBuyer = cls.appendField('buyer')
        cls.iDebt = cls.appendField('debt')
        cls.iPhone = cls.appendField('phone')
        cls.iPaperSize = cls.appendField('paper_size')
        cls.iSavePaper = cls.appendField('save_paper')
        
        cls.refreshDataList()
            
    @classmethod
    def getTableSale(cls):
        return cls.findObj(1)
    
    @classmethod
    def getPaperSize(cls):
        obj = cls.getTableSale()
        if obj.getValue(cls.iPaperSize) == 1:
            return (241, 280)
        elif obj.getValue(cls.iPaperSize) == 2:
            return (241, 140)
        elif obj.getValue(cls.iPaperSize) == 3:
            return (241, 93)
        elif obj.getValue(cls.iPaperSize) == 4:
            return (241, 70)
            
        return (240, 140)
    
    @classmethod
    def getNormalLineNum(cls):
        d = {1:10, 2:10, 3:10, 4:10}

        obj = cls.getTableSale()
        return d.get(obj.getValue(cls.iPaperSize) ,10)
    
    @classmethod
    def getMaxLineNum(cls):
        '''除了订单行外，最大行数'''
        d = {1:18, 2:18, 3:18, 4:18}

        obj = cls.getTableSale()
        return d.get(obj.getValue(cls.iPaperSize) ,10)
        
    @classmethod
    def updateData(cls, title, footer, buyer, debt, phone):
        '''废弃'''
        obj = cls.getTableSale()
        obj.m_fieldVals[cls.iTitle] = title
        obj.m_fieldVals[cls.iFooter] = footer
        
        if (buyer):
            obj.m_fieldVals[cls.iBuyer] = 'T'
        else:
            obj.m_fieldVals[cls.iBuyer] = 'F'
            
        if (debt):
            obj.m_fieldVals[cls.iDebt] = 'T'
        else:
            obj.m_fieldVals[cls.iDebt] = 'F'
                
        if (phone):
            obj.m_fieldVals[cls.iPhone] = 'T'
        else:
            obj.m_fieldVals[cls.iPhone] = 'F'
            
        obj.update()
    
    
    
    @classmethod
    def isOmitHead(cls):
        return cls.getTableSale().getValue(cls.iSavePaper) == 2
        
    @classmethod
    def isSavePaper(cls):
        return cls.getTableSale().m_fieldVals[cls.iSavePaper]
        
    
    @classmethod
    def includeBuyer(cls):
        return cls.getTableSale().m_fieldVals[cls.iBuyer]
        
        if (cls.getTableSale().m_fieldVals[cls.iBuyer] == 'T'):
            return True
        else:
            return False
        
    @classmethod
    def includeDebt(cls):
        return cls.getTableSale().m_fieldVals[cls.iDebt]
        
        if (cls.getTableSale().m_fieldVals[cls.iDebt] == 'T'):
            return True
        else:
            return False
    
    @classmethod
    def includePhone(cls):
        return cls.getTableSale().m_fieldVals[cls.iPhone]
        
        if (cls.getTableSale().m_fieldVals[cls.iPhone] == 'T'):
            return True
        else:
            return False
                    
class CRelModel(CDataModel):
    '''对应数据库中描述实体关系的表，该类表，并不需要全部显示，仅只在条件查询时用，
        并不提供本地缓存
    '''
    
    tbl = ''
    m_fieldNames = []
    fi_key = -1
    
##    def __init__(self):
##        if self.__class__ is CDataModel:
##            abstract()
##
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def fetchObjs(cls, filterSql):
        '''从数据库中获取数据
            return - objList fetched from DB
        '''
        objList = []
        sql = u'select '+','.join(cls.m_fieldNames)+' from '+ cls.tbl
        if (filterSql != ''):
            sql += ' where ' + filterSql
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return None
        result = dbmngr.dbmngr.cursor.fetchall()
        for item in result:
            obj = cls()
            for j in range(len(item)):
                obj.m_fieldVals[j] = item[j]
            objList.append(obj)
        return   objList
    
    @classmethod
    def concat(cls, filter):
        '''filter - {COL_xx:xx,}
            return - string
        '''
        str = ''
        for col,val in filter.items():
            str += '%s=%s and ' %(cls.m_fieldNames[col], str(val))
        
        return str[:-5]
    
    @classmethod
    def fetchRows(cls, filterSql='', fields=[]):
        '''从数据库中获取数据
            filterSql - string
            fields - [COL_xx,]
            return - objList fetched from DB
        '''
        if (len(fields) == 0):
            sql = 'select '+','.join(cls.m_fieldNames)+' from '+ cls.tbl
        else:
            sql = 'select '
            for i in range(len(fields)):
                sql += '%s,' %cls.m_fieldNames[fields[i]]
           
            sql = sql[:-1] + ' from ' + cls.tbl
            
        if (filterSql != ''):
            sql += ' where ' + filterSql
            
        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt == 0):
##            return []
 
        return dbmngr.dbmngr.cursor.fetchall()
            
class CRelCustDlvy(CRelModel):
    tbl = 'custDlvy'
    m_fieldNames = ['custId', 'dlvyId']
    m_fieldDefVals = ['']*2

    [COL_CUSTID, COL_DLVYID] = [0, 1]
    
    @classmethod
    def getDlvy(cls, custId):
        objList = []
        
        filterSql = 'custId=%d' %custId      
        result = cls.fetchRows(filterSql, [CRelCustDlvy.COL_DLVYID])
        print result
        for row in result:
            dlvyId = row[0]
            objList.append(CDataDelivery.getFirstInstance({CDataDelivery.COL_ID:dlvyId}))
    
        return objList
 
class CRelFundCustomer(CRelModel):
    tbl = 'fund_customer'
    m_fieldNames = ['billId', 'voucherId', 'money']
    m_fieldDefVals = ['']*3
    [COL_BILLID, COL_VOUCHERID, COL_MONEY] = [0,1,2]
    
    listenerList = []

    def getPeerId(self):
        voucherObj = CDataVoucher.findObj(self.getValue(self.COL_VOUCHERID))
        return voucherObj.getValue(voucherObj.iPeerId)

            
    @classmethod
    def getPaidAmount(cls, saleId):
        sql = "select sum(money) from %s where billId='%s'" %(cls.tbl, saleId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return 0
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return 0
        return rows[0][0]
    
    @classmethod
    def getVoucherAlloct(cls, voucherId):
        sql = "select sum(money) from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return 0
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return 0
        return rows[0][0]
        
    @classmethod
    def getBillId(cls, voucherId):
        ids = []
        sql = "select billId from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt >= 1):
            ret = dbmngr.dbmngr.cursor.fetchall()
            for i in range(cnt):
                ids.append(ret[i][0])
        return ids

    @classmethod
    def getAllocateInfo(cls, voucherId):

        sql = "select billId,money from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
    
    @classmethod
    def getPaidList(cls, orderId):
        sql = "select voucherId,money from %s where billId='%s'" %(cls.tbl, orderId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
         
class CRelFundSupplier(CRelModel):
    tbl = 'fund_supplier'
    m_fieldNames = ['billId', 'voucherId', 'money']
    m_fieldDefVals = ['']*3
    [COL_BILLID, COL_VOUCHERID, COL_MONEY] = [0,1,2]

    listenerList = []
    
    def getPeerId(self):
        voucherObj = CDataVoucherSupplier.findObj(self.getValue(self.COL_VOUCHERID))
        return voucherObj.getValue(voucherObj.iPeerId)
    
    @classmethod
    def getPaidAmount(cls, billId):
        sql = "select sum(money) from %s where billId='%s'" %(cls.tbl, billId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return 0
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return 0
        return rows[0][0]
    
    @classmethod
    def getBillId(cls, voucherId):
        ids = []
        sql = "select billId from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt >= 1):
            ret = dbmngr.dbmngr.cursor.fetchall()
            for i in range(cnt):
                ids.append(ret[i][0])
        return ids
    
    @classmethod
    def getAllocateInfo(cls, voucherId):
        sql = "select billId,money from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
    
    @classmethod
    def getPaidList(cls, orderId):
        sql = "select voucherId,money from %s where billId='%s'" %(cls.tbl, orderId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
 
class CDataExpense(CDataModel):
    fields = [('id', None), ('date', None), ('amount', 0.00), ('account_id', None),
            ('subject', None), ('memo', ''), 
            ('creater', None), ('create_time', None),
            ('updater', None), ('update_time', None),
            ('deleter', None), ('delete_time', None), ('delete_reason',''),]
            
    tbl = 'expense'

    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    
    
    autoId = True
    fi_key = 0
    COL_KEY = 0
    
    @classmethod
    def initClsData(cls):   
        cls.m_fieldNames = [field for field,val in cls.fields]

    def __init__(self):
        #self.isNew = True
        for field,val in self.fields:
            self.__dict__[field] = val
            
        #self.m_fieldVals = [val for field,val in self.fields]
    
    @classmethod
    def getExpenseList(cls, subject=None, dateStart=None, dateEnd=None, 
                        delete=0, queryDate='update_time'):
        
        if delete:
            filter = 'where a.deleter is not null'
        else:
            filter = 'where a.deleter is null and '
            if subject:
                filter += ' a.subject=%d and'%subject
                
            if dateStart:
                filter += ' a.%s between "%s" and "%s" and'%(queryDate, dateStart, dateEnd)
            
            filter = filter[:-3] if subject or dateStart else ''
                        
        sql = '''select a.id,a.date,f.briefName,a.amount,b.name,a.memo,
                        c.name,a.create_time,d.name,a.update_time,
                        e.name,a.delete_time,a.delete_reason
                 from expense a left join account_subject b on a.subject = b.id
                                left join staff c on a.creater = c.id 
                                left join staff d on a.updater = d.id
                                left join staff e on a.deleter = e.id
                                left join account f on a.account_id = f.id
                %s
                order by a.date desc''' %(filter)
                
        #print sql
        dbmngr.dbmngr.execute(sql)
        result = dbmngr.dbmngr.cursor.fetchall()
        #print result
        return result
    
    @classmethod
    def getObj(cls, keyVal):
        '''查找对象，只能通过key查找，并且为了避免同步问题，必须从数据库读取
        '''
        sql = 'select %s from %s where id=' %(','.join(cls.m_fieldNames), cls.tbl)
        sql += '%s'
        param = (keyVal)
        cnt = dbmngr.dbmngr.execute(sql, param)
        if cnt <=0:
            print u'查找不到id为%s的数据' %keyVal
            return None
        
        result = dbmngr.dbmngr.cursor.fetchall()
        print result
        
        obj = cls()
        for i,val in enumerate(result[0]):
            obj.__dict__[cls.fields[i][0]] = val
        return obj
        
    @classmethod
    def __fields_to_sql(cls, fields):
        
        sql = ''
        for field,val in fields.items():
            if val is not None:# include:'',0
                sql += field +'=' 
                if (isinstance(val, str) | 
                    isinstance(val, unicode)|
                    isinstance(val, datetime.date)):
                    sql += "'%s'," %(val)
                else:
                    sql += "%s," %(val)
            else:
                sql += field+'=NULL,'

        return sql[:-1]
    
        
    @classmethod
    def update_item(cls, keyVal, fields):
        '''update
        
            fields - {'field':val,}
        '''
        
        sql = 'update %s set %s where id=' %(cls.tbl, cls.__fields_to_sql(fields))
        sql += '%s'
        
        param = (keyVal)
        return dbmngr.dbmngr.execute(sql, param)           
    
    def __update(self, fields=None):
        self.update_item(self.id, {field:self.__dict__[field] for field,defval in self.fields})
                 
    @classmethod
    def delete_item(cls, id, reason=''):
        cls.update_item(id, {'deleter':config.Cfg.getCurUser().getKeyVal(),
                            'delete_reason':reason, 
                            'delete_time':datetime.datetime.now()})
    
    
        
    def delete(self, reason=''):
        self.deleter = config.Cfg.getCurUser().getKeyVal()
        self.delete_time = datetime.datetime.now()
        self.delete_reason = reason
        self.update()
        
    def save(self):        
        
        if not self.id:
            self.create_time = datetime.datetime.now()
            self.update_time = datetime.datetime.now()
            self.creater = config.Cfg.getCurUser().getValue(CDataStaff.COL_ID) 
            self.__insert()
        else:
            self.update_time = datetime.datetime.now()
            self.updater = config.Cfg.getCurUser().getValue(CDataStaff.COL_ID)
            self.__update() 
        
        return self
     
    def __insert(self):
        '''Note:增、删、改都要考虑到其他相关表的信息是否需要修改'''
        
        valList = []
        strField = strVal = ''
        for field,val in self.fields:
            if field == 'id':
                continue
            strVal += '%s,'
            strField += field + ','
            valList.append(self.__dict__[field])
            
        sql = 'insert into %s (%s) values(%s)' %(self.tbl, strField[:-1], strVal[:-1])
        
        #print (sql, self.m_fieldVals)
        cnt = dbmngr.dbmngr.execute(sql, valList)

        #if (cnt > 0):
            #self.__class__.m_dataList.append(self)
            #self.populateExtra()
            #self.notify('insert')
            
        return cnt
    
    
        
    def update(self, cols = []):
        '''设置好数据后，执行该函数以便更新数据库
            cols - [COL_xx,]
        '''
        import datetime
        
        #self.populateExtra()
        #self.notify('update')
        
        if (len(cols) == 0):
            #update all
            cols = [i for i in range(len(self.m_fieldNames))]
        
        sql = 'update  ' + self.tbl + ' set '    
        for i in cols:
            # 最初是只有update非None字段，后来有需要将非None设置成None，所以
            # 该段代码也可以改为
            if (self.m_fieldVals[i] is not None):
                sql += '%s=' %(self.m_fieldNames[i])
                #print type(self.m_fieldVals[i])
                if (isinstance(self.m_fieldVals[i], str) | 
                    isinstance(self.m_fieldVals[i], unicode)|
                    isinstance(self.m_fieldVals[i], datetime.date)):
                    sql += "'%s'," %self.m_fieldVals[i]
                else:
                    sql += '%s,' %(str(self.m_fieldVals[i]))
            elif (self.m_fieldDefVals[i] is None):
                sql += self.m_fieldNames[i]+'=NULL,'
            else:
                sql += '%s=%s,' %(self.m_fieldNames[i], str(self.m_fieldDefVals[i]))
                    
        sql = sql[:-1] + ' '
        sql += 'where %s=' %(self.m_fieldNames[self.COL_KEY])
        sql += '%s'
        param = (self.m_fieldVals[self.COL_KEY])
        return dbmngr.dbmngr.execute(sql, param)
    
class CDataVersionTable(CDataModel):
    tbl = u'version_table'
    fields = [('id', -1), ('table', ''), ('update_time', None),]
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    m_dataList = []
    
    autoId = True
    fi_key = 0
    COL_KEY = 0
    
    def __init__(self):
        self.isNew = True
        for field,val in self.fields:
            self.__dict__[field] = val
            
    @classmethod
    def factory(cls):
        return cls()
    
    @classmethod
    def isOutofDate(cls, table):
        sql = 'select update_time from version_table where table=%s'%(table)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt==0:
            return False
        
        result = dbmngr.dbmngr.cursor.fetchall()

    
        
class CDataStaffOperation(CDataModel):
    tbl = 'staff_operation'
    m_dataList = []
    m_fieldNo = {}
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'title','footer','buyer','debt']
    #[iId, iTitle, iFooter, iBuyer] = [i for i in range(4)]
    autoId = True
    fi_key = 0
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iName = cls.appendField('name')
        cls.iInfo = cls.appendField('info')
        
class CDataStaffGroup(CDataModel):
    tbl = 'staff_group'
    m_dataList = []
    m_fieldNo = {}
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'title','footer','buyer','debt']
    #[iId, iTitle, iFooter, iBuyer] = [i for i in range(4)]
    autoId = True
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iName = cls.appendField('name')
    
    @classmethod
    def add(cls, name):
        '''新增加员工组
        
            新建员工组，并把权限全部置零
        '''       
        obj = cls.quickInsert({cls.iName:name})
        
        CDataPrivilege.resetGroup(obj.getValue(obj.iId), 0, True)
                        
class CDataPrivilege(CDataModel):

    @classmethod
    def get(cls, groupid):
        sql = '''select operation_id from staff_privileges 
                where group_id=%d and allowed=1'''%(groupid)
                
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
        
        
    @classmethod
    def set(cls, op, val, groupid):
        
        #groupid = config.Cfg.getCurUser().getValue(CDataStaff.COL_GROUPID) 
        sql = '''update staff_privileges set allowed=%d 
                where operation_id='%s' and group_id=%d'''%(val, op, groupid)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt==0:
            sql = '''insert into staff_privileges (group_id,operation_id,allowed) values
                (%d, '%s', %d)'''%(groupid, op, val) 
            cnt = dbmngr.dbmngr.execute(sql)
            if cnt == 0:
                return False
        return True
    
    @classmethod
    def resetGroup(cls, group_id, val, insert=False):
        
        if insert:
            sql = '''INSERT into staff_privileges (group_id,operation_id,allowed)  
                        (select %d, name, %d from staff_operation)'''%(group_id, val)
        else:
            sql = '''update staff_privileges set allowed=%d where group_id=%d'''%(val, group_id)
        cnt = dbmngr.dbmngr.execute(sql)
     
    @classmethod
    def requestAuth(cls, op, slient=False):
        
        
        group_id = config.Cfg.getCurUser().getValue(CDataStaff.COL_GROUPID) 
        
        sql = '''select allowed from staff_privileges 
                where group_id=%d and operation_id="%s"'''%(group_id, op)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            passed = False
            
        passed = dbmngr.dbmngr.cursor.fetchall()[0][0]
        
        if not passed and not slient:
            import wx
            dlg = wx.MessageDialog(None, u'没有操作权限，需要权限%s'%op, u'提示',
                                    wx.OK|wx.ICON_ERROR)
            dlg.ShowModal()
            return 
        
        return passed
              
def dataModelInit():
    CDataProduct.refreshDataList()
    CDataCategory.refreshDataList()
    CDataKeyword.refreshDataList()
    CDataDelivery.refreshDataList()
    CDataBackup.refreshDataList()
    CDataCompany.refreshDataList()
    CDataStaff.refreshDataList()
    CDataAccount.refreshDataList()
    CDataAccountDetail.refreshDataList()
    
    #CDataSaleOrder.refreshDataList()
    CDataSaleOrder.initClsData()
    #CDataVoucher.refreshDataList()
    #CDataOperator.refreshDataList()
    
    CDataCustomer.initClsData()
    #CDataSupplier.refreshDataList()
    CDataSupplier.initClsData()
    
    #CDataPurOrder.refreshDataList()
    CDataPurOrder.initClsData()
    
    CDataPurDetail.refreshDataList()
    
    CDataEvent.refreshDataList()
    
    #CDataTableSale.refreshDataList()
    CDataTableSale.initClsData()
    
    CDataVoucher.initClsData()
    CDataVoucherSupplier.initClsData()
    
    CDataCustomerContact.initClsData()
    CDataCustomerType.initClsData()
    CDataCustomerLabel.initClsData()
    CDataCustomerLabeling.initClsData()
    CDataCustomerRegion.initClsData()
       
    CDataVersion.initClsData()
    
    CDataRecord.initClsData()
    
    CDataPeer.initClsData()
    CDataPeerLend.initClsData()
    CDataPeerFund.initClsData()
    
    CDataLog.initClsData()
    CDataTip.initClsData()
    
    #CDataSaleOrderTemp.refreshDataList()
    CDataSaleOrderTemp.initClsData()
    
    CDataProLabel.refreshDataList()
    # for Test,  need sync while running
    CDataProLabel.populateClsData()

    CDataOrderState.initClsData()
    
    CDataAccountSubject.initClsData()
    
    CDataDeliveryPlan.initClsData(True)
    CDataDeliveryRoute.initClsData(True)
    CDataDeliveryRouteRegion.initClsData()
    CDataDeliveryState.initClsData(True)
    
    CDataTruck.initClsData(True)
    
    CDataExpense.initClsData()
    
    CDataStaffOperation.initClsData()
    CDataStaffGroup.initClsData()
    
    # 开始observer事件通知
    CDataModel.enableObserver = True